Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 11:12am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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  
v2
Comments
Skippy II at 27-Dec-12 16:25pm
   
The values are all of int type and I need the sum of them.
Mika Wendelius at 27-Dec-12 16:30pm
   
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 at 27-Dec-12 18:00pm
   
See the updated solution.
Skippy II at 28-Dec-12 5:48am
   
Thanks for the article! Nice work!
But... I'm using a function in which I cannot use the EXECUTE statement... :-(
Mika Wendelius at 28-Dec-12 5:57am
   
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 at 29-Dec-12 7:41am
   
5'ed!
Mika Wendelius at 29-Dec-12 7:52am
   
Thanks Espen :D
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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  
Comments
Skippy II at 27-Dec-12 16:23pm
   
I'm using it in a function, not a stored procedure. Therefore I cannot make use of the Execute statement.
Zoltán Zörgő at 27-Dec-12 16:29pm
   
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?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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  

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

  Print Answers RSS
0 OriginalGriff 490
1 Maciej Los 305
2 Richard MacCutchan 270
3 Mathew Soji 220
4 BillWoodruff 210
0 OriginalGriff 8,834
1 Sergey Alexandrovich Kryukov 7,477
2 DamithSL 5,689
3 Maciej Los 5,329
4 Manas Bhardwaj 4,986


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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100