Dynamic Colomns in query MS SQL 2005

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')

Thanks
LVL 3
QuinnDesterAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
QuinnDesterConnect With a Mentor Author Commented:
I have removed the dynaimc SQL completly and i am using CASE for selects and if statments for the updats to select the correct columns... 20 times more code, but much more managable, and definatly less problematic
0
 
mayank_joshiCommented:
this should work:-

set @avgstatus =(select dbo.avg1(cast(@col as varchar (max)),updatedcount)  from activity_history where Coordinates =cast(@Coordinates  as varchar (max)) )

Open in new window

0
 
QuinnDesterAuthor Commented:
that wont work as it then doesnt see @col as a column
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
QuinnDesterAuthor Commented:
that will work writen as dynamic sql but  i cant set the variable @avgstatus with dynamic SQL @avgstatus is not available after it is executed
0
 
mayank_joshiCommented:
you can use sp_executesql

0
 
mayank_joshiCommented:
A Simple Example:-

DECLARE @i INT, @sql NVARCHAR(512) ,@dbname NVARCHAR(50)

SET @dbname ='master'
 
    SET @sql = N'SELECT @i = COUNT(*) FROM ' 
        + @dbname + '.INFORMATION_SCHEMA.TABLES' 
 
    EXEC sp_executesql 
        @query = @sql, 
        @params = N'@i INT OUTPUT', 
        @i = @i OUTPUT 
 
    PRINT @i 

Open in new window

0
 
mayank_joshiCommented:
Following may be helpful:-

http://support.microsoft.com/kb/262499
0
 
QuinnDesterAuthor Commented:
thanks you, i will look into that.. i have just found that i can declare set and use a variable within a single dynamic query, that gives me the result i need now...  i will look at what youposted though as a better way would be very helpful.

this is what i have just got to work

set @sql = 'declare @avgstatus decimal(12,2) set @avgstatus = (select dbo.avg1(' +
cast(@col as varchar (max))+' ,updatedcount)  from activity_history where Coordinates = '''+cast(@Coordinates  as varchar (max))+''')

update planets set [status] = @avgstatus where Coordinates = '''+cast(@Coordinates  as varchar (max))+''''
0
 
QuinnDesterAuthor Commented:
@mayank_joshi:

I am still struggling to see how that will help me with using the table name dynamicly
the table name is still in a varible... Unless i am missing something in your example.

could you clarify how this would be used to set the table to be updated/select in a query without using dynamic sql?
0
 
QuinnDesterAuthor Commented:
Expert stopped answering my question, and i found a solution myself
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.