Post reply

Table name as variable

  • 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

  • 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

  • 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.

  • November 5, 2007 at 9:54 am

    #748317

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

    -Simon Doubt

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

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