Declare a 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

General SQL Server Forums

New to SQL Server Programming

Declare a Variable

Author

Topic

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-03-26 : 06:13:46

Can I declare a variable ... but the variable needs to be the name of the table ... so that I can change the table more easily ...

10x ! :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-03-26 : 06:21:43

Only by using dynamic sql-statements.
declare @tablename varchar(255)
declare @sql varchar(2000) -- varchar(max) if sql server 2005...
select @tablename = 'yourtable'
select @sql = 'select * from '+@tablename
exec(@sql)

No, you're never too old to Yak'n'Roll if you're too young to die.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-26 : 06:22:07

You can, but how are you intending to use it?


declare @Tabname varchar(100)

set @Tabname = 'TableName'

Select name from sys.columns where object_id in (select object_id from sys.objects where name = '' + @Tabname + '')

Will allow you to look for columns in a table depending on the value of your variable.

where as:


declare @Tabname varchar(100), @SqlCmd nvarchar(max)

set @Tabname = 'TableName'

set @SqlCmd = 'select * from ' + @Tabname

exec sp_executesql @SqlCmd

Will allow you to select from any table in the variable. Before using the second optioon, please read up on dynamic SQL and the security issues you need to take into account.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-26 : 08:27:38

Make sure you read this www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources