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

• Johan Bijnens

  SSC Guru

  Points: 135265

  More actions

  May 28, 2004 at 4:44 am

  #508154

  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

  May 28, 2004 at 5:39 am

  #508166

  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

  May 31, 2004 at 3:13 am

  #508358

  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.