Query by Column Name - 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

Old Forums

CLOSED - General SQL Server

Query by Column Name

Author

Topic

superuser
Starting Member

2 Posts

Posted - 2006-09-06 : 06:51:28

Hi Friends!

I am trying to query a certain column that matches the current month, but it is not working correctly. The columns are named by the month. The below will not pull the data only the column name.



------------does not work----------------
declare @m as varchar(3)
set @m = left(getdate(),3)--    @m = Sep
select @m, dept from t_DLEUBudget
-----------------------------------------
 
-------------works great-----------------
select Sep, dept from t_DLEUBudget
-----------------------------------------

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 06:57:58

Your data is not normalized, hence you must use DYNAMIC SQL, which most of the time is bad design.
See http://www.sommarskog.se/dynamic_sql.html

declare @sql varchar(8000), @m char(3)

set @m = datename(month, getdate())

set @sql = 'select ' + @m + ', dept from t_DLEUBudget'

exec (@sql)

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-06 : 06:58:27

you need use dyanmic sql for this.


Declare @Qry varchar(1000)
declare @m as varchar(3)
set @m = left(getdate(),3)--    @m = Sep
Set @Qry = 'select ' +  @m + ', dept from t_DLEUBudget'
Exec(@Qry)

Chirag

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-06 : 07:00:31