Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dynamic Colomns in query MS SQL 2005

Posted on 2011-03-13
10
Medium Priority
?
294 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:QuinnDester
  • 6
  • 4
10 Comments
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35121549
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
 
LVL 3

Author Comment

by:QuinnDester
ID: 35121579
that wont work as it then doesnt see @col as a column
0
 
LVL 3

Author Comment

by:QuinnDester
ID: 35121590
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35121612
you can use sp_executesql

0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35121616
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
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35121625
Following may be helpful:-

http://support.microsoft.com/kb/262499
0
 
LVL 3

Author Comment

by:QuinnDester
ID: 35121649
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
 
LVL 3

Author Comment

by:QuinnDester
ID: 35121738
@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
 
LVL 3

Accepted Solution

by:
QuinnDester earned 0 total points
ID: 35225768
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
 
LVL 3

Author Closing Comment

by:QuinnDester
ID: 35292434
Expert stopped answering my question, and i found a solution myself
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question