Setting variable as column name.

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)

Setting variable as column name.

Author

Topic

davidmal
Starting Member

19 Posts

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

Hi,
I hope I explain this correctly. I don't need a specific problem resolved (that I could realistically go into). Just need to know if there is there a way to substitute a local variable as a database object, in this case a table column? Let me give you a scaled down example.

Table
dbo.TestDateID
|-------------------------------------------------=----------------|
|TestDateID(int PK)|MathTest(bit)|ReadingTest(bit)|WritingTest(bit)|
|------------------------------------------------------------------|
|45 |1 |0 |0 |
|------------------------------------------------------------------|
|33 |0 |1 |1 |
|------------------------------------------------------------------|
...
...

If the proc passed a parameter TestTypeID 1, 2 or 3 (Math,Reading,Writing), is there a way to do something like...
DECLARE @column (object???)
SET @column =
CASE
WHEN @TestTypeID = 1 THEN 'MathTest'
WHEN @TestTypeID = 2 THEN 'ReadingTest'
WHEN @TestTypeID = 3 THEN 'WritingTest'
END

Select @column from dbo.TestDateID

The main thing is, can you make the column in the select statement above a variable somehow???

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-15 : 23:52:29

If you really want to do this, you have to use Dynamic SQL. see http://www.sommarskog.se/dynamic_sql.html


DECLARE @column varchar(100)
SET @column =
CASE
WHEN @TestTypeID = 1 THEN 'MathTest'
WHEN @TestTypeID = 2 THEN 'ReadingTest'
WHEN @TestTypeID = 3 THEN 'WritingTest'
END
declare @sql varchar(1000)
select @sql = 'Select ' + @column + ' from dbo.TestDateID'
exec (@sql)

davidmal
Starting Member

19 Posts

Posted - 2007-03-16 : 00:12:01

This is such an GREAT forum!!! I'm not the best at explaining exactly what I need but I always get a quick, professional and correct reply.

Thanks khtan. Thanks to all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources