Referencing a table with a table variable

Declan_Basile
Declan_Basile used Ask the Experts™
on
In SQL Server 2012, Is there a way to create a table variable that references a table already in the database?  Note: I have a string variable that contains the name of the table.

DECLARE @tablename As varchar(MAX)
DECLARE @TB As Table

SET @tableName = 'table1'
SET @TB = table with name 'table1'
SELECT * FROM @TB

Note: I'm asking if I could do this without dynamic sql,  I already know that I could do 'EXEC sp_executesql 'SELECT * FROM ' + @tableName.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
No. Object names need to be fixed (static) in the constructed SQL. You cannot use a variable as table name. you need to use dynamic SQL.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Nope, can't do it.

You could use a synonym, but even the synonym would have to be dynamically set.

Author

Commented:
Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial