Table name as variable

• Simon E Doubt

  Newbie

  Points: 0

  More actions

  November 5, 2007 at 8:43 am

  #179033

  Good morning everyone,

  I am writing a stored procedure that accepts as a parameter the name of a table.

  I have defined that parameter as type VARCHAR(50).

  I get an error message when I pass the parameter in the FROM clause of my SELECT statement.

  Here's what I mean (simplified):

  CREATE PROC dbo.spMyProc

  @Table_Name varchar(50)

  AS

  SELECT *

  FROM @Table_Name

  Any input would be greatly appreciated.

  Thanks,

  Simon Doubt

• @fracionero

  Newbie

  Points: 0

  More actions

  November 5, 2007 at 8:54 am

  #748289

  You must use exec command

  CREATE PROC dbo.spMyProc

  @Table_Name varchar(50)

  AS

  declare @command varchar (500)

  set @command= 'SELECT * FROM ' + @Table_Name

  exec (@command)

  Francisco Racionero
  twitter: @fracionero

• Steve Jones - SSC Editor

  Newbie

  Points: 0

  More actions

  November 5, 2007 at 9:24 am

  #748307

  The way the SQL parser works, it can't compile a plan without having a table name available, so this isn't allowed. Dynamic SQL (EXEC) as shown above is the only way to do this.

• Simon E Doubt

  Newbie

  Points: 0

  More actions

  November 5, 2007 at 9:54 am

  #748317

  Thanks very much for the help, guys - much appreciated.

  -Simon Doubt