Solved

Dynamic Colomns in query MS SQL 2005

Posted on 2011-03-13
10
276 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
RAISERROR WITH NOWAIT 2 24
SQL query to select row with MAX date 7 40
How can I find this data? 3 23
SQL Select Query help 1 34
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

685 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