Click here to Skip to main content
Search within:



Next

How to use a variable in a select statement for a declare cursor
Sign Up to vote bad
good
See more: SQL2005SQLSQL-ServerSQL2008
I have this variable @cols (varchar) which contains the column names of a table I need to query. How can I use this variable in a function to declare a cursor the right way?
 
-- code where I fill up @cols
if len(@cols) = 0
    set @ret = 0
else
    begin
        set @cols = substring(@cols, 2, 1000)
        DECLARE Data CURSOR FOR 
	SELECT @cols from OTCriterium WHERE ParentPK = @PKParentCriterium
	OPEN Data
        -- other stuff
	set @ret = 1
    end
-- other code
 
Thanks in advance!
Posted 27 Dec '12 - 10:12
Skippy II663
Add a Solution


3 solutions

While you can use EXECUTE[^] to run a dynamic SQL statement, I'm really suspicious if this is a good approach.
 
If you dynamically define the result set columns wouldn't this result into a situation where you have difficulties in selecting the values from the cursor since you don't know the columns?
 
Dynamic sql could be feasible when you have changing conditions, but I would consider other kind of approaches for this.
 
-- EDIT
Added a small example: Calculate aggregates for dynamic columns using UNPIVOT[^]
  Permalink  
Posted 27 Dec '12 - 10:21
Mika Wendelius177K
Edited 27 Dec '12 - 12:00
v2
Comments
Skippy II - 27 Dec '12 - 16:25
The values are all of int type and I need the sum of them.
Mika Wendelius - 27 Dec '12 - 16:30
In that case, I think you could do this using UNPIVOT[^]. The idea could be to rotate the columns to rows and then using this result as an inline view you could sum the values. All this could be done in a single query without a cursor.
Mika Wendelius - 27 Dec '12 - 18:00
See the updated solution.
Skippy II - 28 Dec '12 - 5:48
Thanks for the article! Nice work! But... I'm using a function in which I cannot use the EXECUTE statement... :-(
Mika Wendelius - 28 Dec '12 - 5:57
Yes, functions must be deterministic so using execute or similar is prohibited. Don't know the full case but could you use a procedure with output parameters instead or even replace the function with a single SQL statement (if the calculation of SUM was the only thing the function did)?
Espen Harlinn - 29 Dec '12 - 7:41
5'ed!
Mika Wendelius - 29 Dec '12 - 7:52
Thanks Espen :D
Build the query as string and execute it as dynamic sql statement. See this article as a good collection of possibilities: Building Dynamic SQL In a Stored Procedure[^]
  Permalink  
Posted 27 Dec '12 - 10:17
Zoltán Zörgő39.7K
Comments
Skippy II - 27 Dec '12 - 16:23
I'm using it in a function, not a stored procedure. Therefore I cannot make use of the Execute statement.
Zoltán Zörgő - 27 Dec '12 - 16:29
Than you are probably taking the wrong path. What is your final goal than? Why do you need this @col variable at that point anyway, the OTCriterium table doesn't change after all, right? Couldn't you select * and than use the list in the logic after?
Here[^] you have a solution to transform a table row to name-value pairs using the xml support. You can use the same approach to filter out only the fields that are in the list, and summarize their values as you mentioned.
  Permalink  
Posted 27 Dec '12 - 10:36
Zoltán Zörgő39.7K
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 365
1 OriginalGriff 315
2 Slacker007 240
3 Dave Kreskowiak 212
4 Aarti Meswania 210
0 Sergey Alexandrovich Kryukov 8,893
1 OriginalGriff 7,134
2 CPallini 3,678
3 Rohan Leuva 3,036
4 Maciej Los 2,428


Advertise | Privacy | Mobile
Web02 | 2.6.130516.1 | Last Updated 27 Dec 2012
Copyright © CodeProject, 1999-2013
All Rights Reserved. Terms of Use
Layout: fixed | fluid