Using a value as a field name 1

[jeffmoore64]

Hi,
I need to use the result from one query to define the name of the field to use in the surrounding query.

The inner select stmt returns the field name (dcEt5)

select (select Cost_Field  from dbo.Cost_Field_Lookup where MonthNumber = datepart(m,getdate())) from dbo.whm03t

This just returns:
dcEt5 several thousand times

I need the contents of that field...

TIA

 

[wbodger]

Hmm... I would think to do this using 2 queries. First get the field name and then loop thru using it in the second query. Though I don't understand why your solution would not work as well.

 

[ptheriault]

you are going to have to use dynamic sql.

something like this should work.

declare @sql varchar(4000)
,@field varchar(10)

SELECT @field = Cost_Field from dbo.Cost_Field_Lookup where MonthNumber = datepart(m,getdate()) from dbo.whm03t

select @sql = 'select '+@field+' from mytable'

exec(@sql)

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jeffmoore64]

Very Nice!!!

Thanks

Heres a star