Sql - tablename as variable

Question

Hi folks,

i am trying to execute this query:

declare @tablename varchar(50)

set @tablename = 'test'

select * from @tablename

This produces the following error:

Msg 1087, Level 16, State 1, Line 5
Must declare the table variable "@tablename".

What's the right way to have table name populated dynamically?

TIA

Answer 1

You can't use a table name for a variable, you'd have to do this instead:

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'SELECT * from yourtable'
EXEC (@sqlCommand)

Answer 2

Table names and column names need to be static, if the query is static. For dynamic table or column names, you should generate the full SQL dynamically, and use sp_executesql to execute it.

More details here: The curse and blessings of dynamic SQL

Answer 3

You'll need to generate the sql dynamically:

declare @tablename varchar(50) 

set @tablename = 'test' 

declare @sql varchar(500)

set @sql = 'select * from ' + @tablename 

exec @sql