Dynamic Colomns in query MS SQL 2005
Posted on 2011-03-13
I have a query that i need to run from a stored procedure, and the column it needs to update/select depends on the hour of the day.
i have used dynamic sql to build up some of my queries and this is working fine, how ever i need a way of creating a query to set a veriable value to be used in a second query, when it gets to the second query it is saying that the veriable needs to be declared.
i get the time of day using
set @thishour = (select top 1 DATEPART(hour, @date))
i have a lookup table that gives me the column name for the hour..
is there a way of casting the result into a column name without using dynamic sql
I have tried this
declare @sql nchar (500)
declare @col varchar (max)
declare @Coordinates nvarchar (50)
declare @avgstatus decimal(12,2)
set @Coordinates = '4:264:6'
set @col = 'five'
set @sql = '(select dbo.avg1(' +
cast(@col as varchar (max))+' ,updatedcount) from activity_history where Coordinates = '''+cast(@Coordinates as varchar (max))+''')'
set @avgstatus = (exec (@sql))
but i get an error at exec (which i expected but had to try)
here is the hard coded code that works fine
set @avgstatus =(select dbo.avg1(five,updatedcount) from activity_history where Coordinates = '4:264:6')