I need a little cursor help

[CK]

Good Morning All,
Can use use a variable for the FOR clause in a cursor?

Example
I have
DECLARE @a varchar(50), @b varchar(50), @c varchar(50)
DECLARE @sql varchar(255)
DECLARE @x varchar(50), @y varchar(50), @z varchar(50)

DELCARE outer_cursor CURSOR FOR
SELECT this, that, other
FROM sometable

OPEN outer_cursor
FETCH NEXT FROM outer_cursor
INTO @a, @b, @c

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'SELECT ' + @a + ', ' + @b + ', ' + @c + ' FROM
someothertable'

--Here is the question. Can I do this? If not how would I do this?
DELCARE inner_cursor CURSOR FOR @sql -- <~~Is this legal?
OPEN inner_cursor
FETCH NEXT FROM inner_cursor
INTO @x, @y, @z

--etc..

Is it legal to do this in SQL? Is there a better way? I know everybody hates
cursors, but I am just curious if this would work.

TIA,
~ck

[Erland Sommarskog]

CK (c_**********@hotmail.com) writes:

DELCARE outer_cursor CURSOR FOR

Before you go any furher, make it a habit to create your cursors STATIC:

DECLARE outer_cursor CURSOR STATIC FOR

This can considerably improve the performance of your cursor operations,
and it also avoid surprises if you update rows in the cursor.

--Here is the question. Can I do this? If not how would I do this?
DELCARE inner_cursor CURSOR FOR @sql -- <~~Is this legal?

No that is not legal, but you can use dynamic SQL for the task. I have
a longer article on dynamic SQL on my web site. This link goes directly
to the cursor section:
http://www.sommarskog.se/dynamic_sql.html#cursor.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

>Can you use a variable for the FOR clause in a cursor? <<

No. But a better question is why you are using a cursor at all.
Given OLAP functions, CASE expressions, etc. in current Standard SQL,
you should only use 1-2 in your entire career at most.

>Is there a better way? I know everybody hates cursors, but I am just curious if this would work. <<

Well, is there a WORSE way? Dynamic SQL declaring cursors is a
nightmare on soooo many levels. Saying SQL people hate cursors is a
mild understatement. What a cursor says is that either the product or
the programmer is so weak that we have to throw away 30 years of RDBMS
and revert to low-level procedural code. And 99.98% of the time when
you see dynamic SQL, it is an admission that the programmer never had
a basic software engineering course and does not know about cohesion.

What is the actual problem you are trying to solve? Or was this a "If
I poison my cattle, will they die?" kind of question?

[Ed Murphy]

CK wrote:

Can use use a variable for the FOR clause in a cursor?

Example
I have
DECLARE @a varchar(50), @b varchar(50), @c varchar(50)
DECLARE @sql varchar(255)
DECLARE @x varchar(50), @y varchar(50), @z varchar(50)

DELCARE outer_cursor CURSOR FOR
SELECT this, that, other
FROM sometable

OPEN outer_cursor
FETCH NEXT FROM outer_cursor
INTO @a, @b, @c

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'SELECT ' + @a + ', ' + @b + ', ' + @c + ' FROM
someothertable'

--Here is the question. Can I do this? If not how would I do this?
DELCARE inner_cursor CURSOR FOR @sql -- <~~Is this legal?
OPEN inner_cursor
FETCH NEXT FROM inner_cursor
INTO @x, @y, @z

--etc..

Is it legal to do this in SQL? Is there a better way? I know everybody hates
cursors, but I am just curious if this would work.

There's a reason everybody hates them; they're easy to screw up, and
slow even if you don't. Do you really need to process rows one at a
time? Even if you do, a simple workaround is to have the inner cursor
just pull all columns from someothertable, then dynamically copy the
relevant ones into your variables.

What is the real-world situation behind this example? Knowing that, we
may be able to give more specific suggestions on avoiding all this mess.

[CK]

How do I write a set based query? I have a groupSets table with fields
setId, idField, datasource, nameField, prefix, active
Data:
1,someIDfield, someTable, someField, pre1, 1
2,someotherIDfield, someTable, someotherField, pre2, 1
3,somethirdIDfield, someTable, somethirdField, pre3, 1
4,somefourthIDfield, someotherTable, somefourthField, pre4, 1

I need to generate records in another table by constructing queries from the
data in groups sets. I need to insert a record for each distinct result of
the query.
Example:
SELECT DISTINCT someIDfield FROM someTable WHERE someIDfield IS NOT NULL

then I need to do an insert for each result of the above query

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result1)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result2)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result3)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, result4)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (1, prefix + nameField, 1, resultN)

--next reord from groupSets
SELECT DISTINCT someotherIDfield FROM someTable WHERE someotherIDfield IS
NOT NULL
INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result1)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result2)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result3)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, result4)

INSERT INTO groups(setId, groupName, active, groupingEntityID)
VALUES (2, prefix + nameField, 1, resultN)
I basically want to do the same operation on each record in the groupSets
table. How can I accomplish this without a cursor? Any ideas?
Thanks for your help,
~ck

"--CELKO--" <jc*******@earthlink.netwrote in message
news:32**********************************@m3g2000h sc.googlegroups.com...

>>Can you use a variable for the FOR clause in a cursor? <<

No. But a better question is why you are using a cursor at all.
Given OLAP functions, CASE expressions, etc. in current Standard SQL,
you should only use 1-2 in your entire career at most.

>>Is there a better way? I know everybody hates cursors, but I am just
curious if this would work. <<

Well, is there a WORSE way? Dynamic SQL declaring cursors is a
nightmare on soooo many levels. Saying SQL people hate cursors is a
mild understatement. What a cursor says is that either the product or
the programmer is so weak that we have to throw away 30 years of RDBMS
and revert to low-level procedural code. And 99.98% of the time when
you see dynamic SQL, it is an admission that the programmer never had
a basic software engineering course and does not know about cohesion.

What is the actual problem you are trying to solve? Or was this a "If
I poison my cattle, will they die?" kind of question?