Table Name as a variable?

Question

Post reply

Table Name as a variable?

Richard Sabbara

Ten Centuries

Points: 1382
More actions
June 11, 2007 at 1:09 pm

#153630

I have the need to compare the data in two tables, to make the SP easier to run, I wanted the table names to be variables, is this possible?
A simpler version of the query that I am trying to run is :

Declare @name as Varchar(80)
Set @name = 'QCP_HRA'
Select top 10 *
from @name
When I run this, I get the following error message:
Server: Msg 137, Level 15, State 2, Line 5
Must declare the variable '@name'.
I know that this is doable, I had to do it in a previous life, I just forgot how.

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

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

Loner SSC-Insane Points: 21279 More actions · Answer 1

You need to use dynamic sql.

Declare @name as Varchar(80), @sql VARCHAR(8000)

Set @name = 'QCP_HRA'

SET @sql = 'SELECT * FROM ' + @name

EXEC (@SQL)

Richard Sabbara Ten Centuries Points: 1382 More actions · Answer 2

Thank you, I knew it was something like that but I could not remember excatly how to do it, this is a really powerful tool, thank you again, it works wonderfully!