Select from a table variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select from a table variable

Author

Topic

klingdon74
Starting Member

2 Posts

Posted - 2007-10-03 : 04:22:16

I want to fetch the table name from a field in table A. And then I want to fetch from that table name. How do I do this?

Example:
DECLARE @TABLE_NAME nvarchar(64)
SET @TABLE_NAME = (SELECT mTableName FROM Maintenance WHERE mID = 1)

SELECT * FROM @TABLE_NAME <-------- Error! Must declare the variable '@TABLE_NAME'.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-03 : 04:26:41

Dynamic sql is way to go here.

Declare @SQL Varchar(8000)

set @SQL = 'Select * from ' + @TABLE_NAME 
Exec(@SQL)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-03 : 04:27:28

You need dynamic sql
Make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-03 : 04:28:23

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources