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

Question

Post reply

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

Don Caldwell

Valued Member

Points: 64
More actions
January 12, 2005 at 9:24 am

#65505

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

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

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

Fred Wahlquist SSC Eights! Points: 874 More actions · Answer 1

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

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

Fred Wahlquist SSC Eights! Points: 874 More actions · Answer 3

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

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.