Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dynamic Colomns in query MS SQL 2005

Posted on 2011-03-13
10
Medium Priority
?
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

661 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