How can I use a table name as a variable in a Select statement?

  • Hello,

    I need to pass table names as variables to a stored procedure. I keep getting this error:

    Server: Msg 137, Level 15, State 2, Line 3

    Must declare the variable '@TableName'.

    This is an example that produces the error when ran in QA:

    Declare @TableName char(10)

    Select @TableName = 'Test'

    Select Top 1 *  From @TableName

    Running on both SQL7 and 2000. Looked through BOL couldn't find answer.

    Sorry for being so dense

    Thanks

    Don

  • Don,

    Here's one method.  Build the SQL statement and use exec to run it.

    CREATE PROCEDURE dbo.GetData

    @TableName varchar(30)

    AS

    DECLARE @SQLcmd as varchar(500)

    set @SQLcmd = 'Select Top 1 *  From ' + @TableName

    execute (@SQLcmd)

    GO

     

    Fred

  • Please try to avoid using dynamic sql. It's a bad coding habit and it exposes you to sql injection (hacker could take control of your whoole domain (not just sql server)).

    Please read this before using this method.

    http://www.sommarskog.se/dynamic_sql.html

  • Wow !!  I just read Erland's article you referred to Remi and I learned so much I feel like I want to send someone some cash.

    No kidding, it was very enlightening and now I have lots of new ways to deal with problems before trying dynamic SQL -- and if I have to use it I can code it much better.

    Thanks for bringing this article to people's attention.

    Fred

  • BTW this is only the tip of the iceberg. Once you understand how powerful and dangerous sql injection can be, you go out of your way to avoid it (unless it's for dba tasks that no normal user will ever see... and that there's no other simple way around it of course).

    You may have read the part where a user could drop a table with injection. But from all I read this is pretty much least of your concerns. I'd be more concern with a user gaining domain admin rights (yeah it's possible... and not that hard)... or on a less apocalyptic note, simply take control on all the server boxes and kicking you out of 'em by removing your logins. Or he could simply ship your customer's personal data to his sql server without ever being caught.

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

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