Use a variable holding a table name in T-SQL? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Use a variable holding a table name in T-SQL?

Author

Topic

niallporter
Starting Member

4 Posts

Posted - 2007-03-15 : 11:55:40

Hi all,

I'm trying to get the following code to work:


DECLARE	@dbName AS varchar(40), @dbSysFilesTbl AS varchar(40)

SELECT	@dbName = 'master'
SELECT	@dbSysFilesTbl = '[' + @dbName + '].[dbo].[sysfiles]'

SELECT	*
FROM	@dbSysFilesTbl

When I execute the code in SQL Query Analyzer it gives the following message:

quote:

Server: Msg 137, Level 15, State 2, Line 7
Must declare the variable '@dbSysFilesTbl'.

The error message is triggered when the code is attempting to use the contents of @dbSysFilesTbl as a table name in the FROM clause of the last SELECT statement. Obviously @dbSysFilesTbl is declared in the first line of the code and the second SELECT statement which populates @dbSysFilesTbl has no problem with the variable, just when it's used in a FROM clause. Am I doing something wrong or can FROM clauses just not see and use the contents of variables properly?

Many thanks in advance,
Niall

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 11:58:10

http://www.sommarskog.se/dynamic_sql.html

Peter Larsson
Helsingborg, Sweden

niallporter
Starting Member

4 Posts

Posted - 2007-03-15 : 12:23:16

Aha, that answers my question nicely - thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources