multiple cursors

Question

I have 3 cursors: DECLARE cursor1 CURSOR FOR SELECT @intVar1 = Col1 FROM Table1;

DECLARE cursor2 CURSOR FOR SELECT @intVar2 = Col1 FROM Table2 WHERE Col2 = @intVar1

DECLARE cursor2 CURSOR FOR SELECT @intVar3 = Col1 FROM Table3 WHERE Col2 = @intVar1

Basically I want to retrieve each Col1 into @intVar1 [using cursor1], then use that to pluck out corresponding rows using cursor2 and cursor3. Is this possible in SQLServer 2005?

Answer 1

0

I know you've solved your answer, but two things -

1) I hope your solution involved ditching the cursors and using set based code

And

2) Seeing as my hopes are likely to be dashed, please can you post your solution here such that someone who finds this page from google in the future finds some useful information...

answered Mar 07 '10 at 07:48 PM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

+1 Valid points. I hope the code gets posted.

Mar 07 '10 at 11:02 PM CirqueDeSQLeil

+1 Admit it, Matt. You want him to post the solution because the triple-cursor is eating away at you. You're just dying to convert it. I don't blame you. :)

Mar 08 '10 at 01:00 AM Tom Staab

(comments are locked)

Answer 2

0

I too hope that the cursors were ditched. If the solution were to be posted, then it is highly likely that somebody here can offer an alternative solution to meet your requirements.

answered Mar 07 '10 at 11:01 PM

CirqueDeSQLeil
3.9k ● 7 ● 11 ● 15

DECLARE cursor1 CURSOR FOR SELECT Col1 FROM Table1;

Open cursor1 Fetch cursor1 into @intVar1; while (blah blah blah) { DECLARE cursor2 CURSOR FOR SELECT Col1 FROM Table2 WHERE Col2 = @intVar1 DECLARE cursor3 CURSOR FOR SELECT Col1 FROM Table3 WHERE Col2 = @intVar1

Fetch cursor2 into @intVar2;
Fetch cursor3 into @intVar3;

while (more blah blah blah)
{
    Fetch cursor2 into @intVar2;
    Fetch cursor3 into @intVar3;
}
close cursor 2;
close cursor 3;
deallocate cursor 2;
deallocate cursor 3;

Fetch cursor1 into @intVar1;

} close cursor 1; deallocate cursor 1;

Mar 08 '10 at 08:37 AM Ahmad Zaman

It looks more readable in the edit field

Mar 08 '10 at 08:38 AM Ahmad Zaman

btw, how do you close a question?

Mar 08 '10 at 08:38 AM Ahmad Zaman

Either check the "answered" checkbox by one of the answers or (in this case) add your own answer and then mark it as the answer.

Mar 09 '10 at 11:14 AM Brimstedt

(comments are locked)

Answer 3

Do you have to use a cursor? There are other solutions to your question, (depending on what you are doing to the @intVar2 and @intVar3) Joins, sub queries, in, exists, etc.

SELECT col1 FROM table2 WHERE col2 IN (SELECT col1 FROM table1)
SELECT col1 FROM table3 WHERE col2 IN (SELECT col1 FROM table1)

OR

SELECT col1 FROM table2 t2 INNER JOIN table1 t1 ON t2.col2=t1.Col1
SELECT col1 FROM table3 t3 INNER JOIN table1 t1 ON t3.col2=t1.Col1

I am not a fan of cursors and that is why I post this answer to give you something to consider. Cursors are usually very slow in SQL server, BUT sometimes a cursor outperforms a bad set based statement.

multiple cursors

Follow this question

Related Questions