Challenge: make this dynamic SQL not dynamic

• Undebtedly

  SSCrazy

  Points: 2938

  More actions

  October 15, 2002 at 8:35 am

  #158510

  We have a table name stored in a variable. So far we've needed dynamic SQL to execute queries against it. For example:

  DECLARE @Qry VARCHAR(512)

  DECLARE @Table VARCHAR(20)

  SET @Table = 'MyTable'

  SET @Qry = 'SELECT * FROM '

  ...

  SET @Qry = @Qry + @Table

  EXEC (@Qry)

  What I want to do is something like this:

  SELECT * FROM (@MyTable)

  Can it be done?

  Steve

  
  
  Steve Miller

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737403

  More actions

  October 15, 2002 at 10:16 am

  #438581

  no

  Steve Jones

  sjones@sqlservercentral.com

  http://www.sqlservercentral.com/columnists/sjones

• Undebtedly

  SSCrazy

  Points: 2938

  More actions

  October 15, 2002 at 11:57 am

  #438592

  Clear, concise, and to the point.

  (Thanks)

  Steve (<sigh>. Now I have to figure out how to code this thing.)

  
  
  Steve Miller

• Simon Sabin

  SSCrazy Eights

  Points: 8142

  More actions

  October 15, 2002 at 3:47 pm

  #438619

  Further more dynamic SQL involving variables should be executed with sp_executesql

  i.e select * from (@table) where column = @value.

  If @table and @value are passed in then the code should be

  declare @sql nvarchar(200)

  set @sql = 'select * from ' + @table + ' where column = @value'

  exec sp_executesql @sql ,N'@value int', @value

  this allows for proper parameterisation by the sql engine

  Simon Sabin

  Co-author of SQL Server 2000 XML Distilled

  http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

  
  Simon Sabin
  SQL Server MVP
  
  http://sqlblogcasts.com/blogs/simons

• Undebtedly

  SSCrazy

  Points: 2938

  More actions

  October 15, 2002 at 3:58 pm

  #438623

  Yup. I was just trying to get an example out the door so people would know what I'm talking about. So I was being a little lazy.

  Steve

  
  
  Steve Miller