Click here to Skip to main content
13,554,805 members
Rate this:
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
        set @cols = substring(@cols, 2, 1000)
	SELECT @cols from OTCriterium WHERE ParentPK = @PKParentCriterium
	OPEN Data
        -- other stuff
	set @ret = 1
-- other code

Thanks in advance!
Posted 27-Dec-12 10:12am
Rate this: bad
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.

Added a small example: Calculate aggregates for dynamic columns using UNPIVOT[^]
Skippy II 27-Dec-12 16:25pm
The values are all of int type and I need the sum of them.
Skippy II 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... :-(
Wendelius 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.
Wendelius 27-Dec-12 18:00pm
See the updated solution.
Espen Harlinn 29-Dec-12 7:41am
Wendelius 29-Dec-12 7:52am
Thanks Espen :D
Wendelius 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)?
Rate this: bad
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[^]
Skippy II 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ő 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
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.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web03 | 2.8.180515.1 | Last Updated 27 Dec 2012
Copyright © CodeProject, 1999-2018
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