x

multiple cursors

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?

more ▼

asked Mar 07 '10 at 03:16 PM in Default

Ahmad Zaman gravatar image

Ahmad Zaman
17 2 2 2

Its Ok, solved the problem...just didn't realise how messy T-SQL could be!
Mar 07 '10 at 04:07 PM Ahmad Zaman
If this is solved, please close the question.
Mar 07 '10 at 07:12 PM Tom Staab
Please add your solution so that others can benefit from your investigations. You may also find that you will have suggestions given to you on how you may be able to consider other options - cursors are very hard on your server performance, 3 of them doubly so.
Mar 08 '10 at 05:54 AM Fatherjack ♦♦

Tom Staab wrote "If this is solved, please close the question."

Heh... if the op is still using a cursor, then this thread should never be closed. He should continue to give us updates on how slow and how many resources his code is using. ;-)
Jun 30 '10 at 03:56 PM Jeff Moden
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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...

more ▼

answered Mar 07 '10 at 07:48 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 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)
10|1200 characters needed characters left

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.

more ▼

answered Mar 07 '10 at 11:01 PM

CirqueDeSQLeil gravatar image

CirqueDeSQLeil
4k 10 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)
10|1200 characters needed characters left

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.

more ▼

answered Mar 09 '10 at 06:12 AM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x57

asked: Mar 07 '10 at 03:16 PM

Seen: 3022 times

Last Updated: Mar 07 '10 at 03:16 PM