asked on

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

mayank_joshi

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

QuinnDester

ASKER

that wont work as it then doesnt see @col as a column

QuinnDester

ASKER

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

mayank_joshi

you can use sp_executesql

mayank_joshi

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

mayank_joshi

Following may be helpful:-

http://support.microsoft.com/kb/262499

QuinnDester

ASKER

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

QuinnDester

ASKER

@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?

ASKER CERTIFIED SOLUTION

QuinnDester

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

QuinnDester

ASKER

Expert stopped answering my question, and i found a solution myself