How to use a variable in a select statement for a declare cursor

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

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?

SQL

-- 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:12am

Skippy II

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2012-12-27T10:21:00

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[^]

Posted 27-Dec-12 10:21am

Wendelius

Updated 27-Dec-12 12:00pm

v2

Comments

Skippy II 27-Dec-12 16:25pm

The values are all of int type and I need the sum of them.

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.

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 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 29-Dec-12 7:41am

5'ed!

Wendelius 29-Dec-12 7:52am

Thanks Espen :D

**Zoltán Zörgő** · Answer 2 · 2012-12-27T10:17:00

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[^]

Posted 27-Dec-12 10:17am

Zoltán Zörgő

Comments

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?

**Zoltán Zörgő** · Answer 3 · 2012-12-27T10:36:00

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.

Posted 27-Dec-12 10:36am

Zoltán Zörgő