Use dynamic table name in sql server query

Question

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.

Tables aren't parameters. The executalbe code generated by the query, ie the execution plan depends on the table schema, indexes and statistics. You don't even need "dynamic tables" anyway, there are far cleaner and easier ways to do whatever it is you want to do. What is it that you want to do? Why don't you use a UNION or a view? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 14, 2018 at 9:50
"dynamic tables" are typically a smell that suggests you want to do something else. Eg implement inheritance, combine multiple tables with similar fields (similar to inheritance) or recombine partitioned tables. There are proper ways to solve the actual problem, eg by using view with UNION or UNION ALL, partitioned views that will automatically select the correct table based on the query criteria, creating a single partitioned table and more. They are a lot cleaner, faster and easier to maintain. Imagine not having to specify the tablename in the query — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 14, 2018 at 9:53

Squirrel · Accepted Answer · 2018-03-14 09:24:38Z

1

you need to use Dynamic SQL

either

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

exec (@sql)

or

exec sp_executesql @sql

answered Mar 14, 2018 at 9:24

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shyam Vemula · Accepted Answer · 2018-03-14 09:40:50Z

0

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

edited Mar 14, 2018 at 9:40

answered Mar 14, 2018 at 9:31

Shyam Vemula

5892 silver badges14 bronze badges

Collectives™ on Stack Overflow

Use dynamic table name in sql server query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related