Referencing tables using variables.

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)

Referencing tables using variables.

Author

Topic

nmarks
Yak Posting Veteran

53 Posts

Posted - 2007-12-17 : 04:38:23

Is it possible to reference to tables using variables? I'd like to use an approach along these lines:

use my_database
declare @tbl_name text
set @tbl_name = 'my_table'
select * from @tbl_name

This approach works for executing sprocs it doesn't work for tables. Please help!

Constructive comments much appreciated.

matty
Posting Yak Master

161 Posts

Posted - 2007-12-17 : 04:43:40

you have to use dynamic sql for it to work..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-17 : 04:44:11

You have to use dynamic SQL for this. like this

use my_database
declare @tbl_name text
set @tbl_name = 'my_table'
Exec('select * from' + @tbl_name)

May i know why you need variables for passing table names?

nmarks
Yak Posting Veteran

53 Posts

Posted - 2007-12-17 : 05:08:57

Both : thanks.

My boss requires version control in the DB I'm writing. He wants tables, sprocs and functions should have names like 'tbl_parameters_1.0.0' or 'sp_calculate_premiums_2.0.3' so they can keep a track of which tables, sprocs and functions constitute a given version of the database.

nmarks
Yak Posting Veteran

53 Posts

Posted - 2007-12-17 : 05:11:00

I've just checked it and your method works. Many thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources