Build dynamic T-SQL

I have a question regarding SQL with MS SQL Server

I have a table as such

customer
id  country_id post_code_id
1      AU          1
2      GB          1

I have postcode tables seperated 开发者_运维百科as such

post_codes_AU
post_codes_GB
etc..

Is there a way I can use country_id in the query to join the right table.. e.g.,

select * from customer c
inner join post_codes_ '+c.country_id+' as pc on pc.id=c.post_code_id 

You can not join the query like this.

To make a proper query you can do one of the following:

• Make a partitioned view over the post_codes_... tables and join this view
• Generate the queries for each country_id in the customer table and glue them together with UNION ALL statements.

You will need to put [] around the table name if it really has a space in the name as your sample query suggests. This looks like a typo so you might not need to do that. Once you do that it should work fine as dynamic SQL.

Be sure that you are aware of potential pifalls of using dynamic SQL before you go that route though. This is worth a read:

http://www.sommarskog.se/dynamic_sql.html

Create a view and join that;

-- #1 create a view . . . .
create view MyPostCodes

as

select
    country_id='AU',
    post_code_id
from
    post_codes_AU
union
select
    country_id='GB',
    post_code_id
from
    post_codes_GB
go
-- #2 now join like this
select * from customer c
inner join MyPostCodes as pc on pc.id=c.post_code_id and pc.country_id=c.country_id

Seems your query will work, changed a little.

Select * from customer c 
Inner Join post_codes
On concat('post_codes_',c.country_id)=Post_codes.post_code_id

Or have a try with below piece of code,

 Select * from customer c
 Inner Join post_codes 
 On right(1-charindex('_',reverse(post_codes_id)),post_codes_id) =c.country_id

Before comparing remove the exact code to compare to.