Varibles in SELECT statements

• Silverado

  Valued Member

  Points: 70

  More actions

  November 13, 2007 at 2:49 pm

  #181139

  CREATE PROC xxxRepVerJoel

  @Table varchar(255)

  AS

  DECLARE @Tablename as varchar(255)

  SET @Tablename = @Table

  SELECT COUNT(*) FROM @Tablename

  GO

  --It gives me an error "Must Declare the Varible '@Tablename'

• Adam Haines

  SSC-Insane

  Points: 23217

  More actions

  November 13, 2007 at 2:56 pm

  #750852

  declare @sql nvarchar(max)

  set @sql = N' SELECT COUNT(*) FROM ' + @Tablename

  exec (@sql)

  ---

  
  My blog: http://jahaines.blogspot.com

• Adam Haines

  SSC-Insane

  Points: 23217

  More actions

  November 13, 2007 at 2:58 pm

  #750854

  A table named cannot be named dynamically via variable unless you use exec or sql_execute

  ---

  
  My blog: http://jahaines.blogspot.com

• Adam Haines

  SSC-Insane

  Points: 23217

  More actions

  November 13, 2007 at 3:05 pm

  #750857

  the other method is actually sp_executesql, which is better because it lets you reuse execution plans and has better parameter capabilities.

  more info here:

  http://msdn2.microsoft.com/en-us/library/ms175170.aspx

  ---

  
  My blog: http://jahaines.blogspot.com

• Silverado

  Valued Member

  Points: 70

  More actions

  November 13, 2007 at 3:50 pm

  #750870

  Thanks for you help. That did it.