CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012
Ravi
Ravi

Reputation: 326

Use dynamic table name in sql server query

I have an Error table which stores the names of the table in which the error occurred. Now I want to query the table using the table name selected from the "Error" table.

I tried to store the table name in a variable and use this variable in the FROM clause in my query. But this doesn't work:

DECLARE @tableName VARCHAR(15)

select @tableName = TableName from SyncErrorRecords where Status = 'Unsynced'

select * from @tableName

Can anyone help me out on this. Thanks in advance.

Upvotes: 2

Views: 6905

Answers (2)

Shyam Vemula
Shyam Vemula

Reputation: 589

The Query as follows.

DECLARE @tableName VARCHAR(15),
        @Qry VARCHAR(4000)
SELECT @tableName = TableName FROM SyncErrorRecords WHERE Status = 'Unsynced'
SET @Qry = 'SELECT * FROM ' +  @tableName
EXEC SP_EXECUTESQL @Qry

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

you need to use Dynamic SQL

either

declare @sql nvarchar(max)
select @sql = 'select * from ' + quotename(@tableName)

exec (@sql)

or

exec sp_executesql @sql

Upvotes: 1

Related Questions