Subquery in from clause or Case in from clause

Question

Post reply

Subquery in from clause or Case in from clause

Ryk Neethling

Old Hand

Points: 380
More actions
May 28, 2004 at 2:53 am

#108343

I have the following which I want to achieve.
I have several tables with the exact same structure, but each table is for a differnt country, so I would like to have my from clause to be dynamic.
this is what I would like to do, but cant get it working, can anyone help please.
DECLARE @C VARCHAR(20)
SET @C = 'NZ'
SELECT ID
FROM CASE
( WHEN @C = 'AU' THEN tbl_Au
WHEN @C = 'NZ THEN tbl_Nz) AS temptable
Thank you,
Ryk

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135364 More actions · Answer 1

add a country-code column to your tables and create a partitioned view.

This way sqlserver may serve you best if you want to avoid dynamic sql.

check BOL

With dynamic sql you might compose the sqlstatement using a case statement and then execute it.

e.g.

select @sqlstmt = 'select id from '

+ case @C when 'AU' THEN 'tbl_Au'

WHEN 'NZ' THEN 'tbl_Nz'

end

exec @sqlstmt

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Ryk Neethling Old Hand Points: 380 More actions · Answer 2

That is actually part of a stored procedure call.It will receive it from a ASP webpage and then pass in the country variable, then I want it to decide which table to use in the insert statement.

Ryk

Amit Jethva SSCarpal Tunnel Points: 4707 More actions · Answer 3

you have ideal situation for a partitioned view.

otherwise, consider this. (increase the code length for all other countries .....)

DECLARE @C VARCHAR(20)

-- SET @C = 'NZ'

IF @C = 'AU'

SELECT ID FROM tbl_Au As temptable

else

if @C = 'NZ'

SELECT ID FROM tbl_Nz AS temptable

-- Amit

"There is no 'patch' for stupidity."

Download the Updated SQL Server 2005 Books Online.