CODEWITHSUNDEEP
sql-server-2005stored-procedurescursor
Sreedhar Danturthi
Sreedhar Danturthi

Reputation: 7571

Handling multiple cursors in stored procedure

I have two cursors (say CRSX1, CRSX2) in a stored procedure. It is like a nested while loop logic I need to implement. Can I use the second cursor CRSX2 while CRSX1 is open or do I have to close the first cursor before using second cursor.

Or is there any other way around if we have to implement logic for double while loop in SQL Server

Thanks in advance

Upvotes: 1

Views: 18626

Answers (3)

AdaTheDev
AdaTheDev

Reputation: 147234

You can nest cursors, but you should be looking to remove those cursors in favour of a SET-based operation if you can. Otherwise, it's likely performance is not going to be great, especially with larger volumes of data. I'd only use cursors / loop as a last resort.

Further explanation:
Using cursors means you will be iterating round, row by row to perform a certain operation (RBAR - Row By Agonizing Row). This is not as efficient as performing the operation in a single, non-looping manner over a set of data in one go (i.e. set-based).

e.g. a crude example, to count the number of rows in a table.

Option 1: Nasty RBAR way, open a cursor on the table, loop round and increment a counter variable.

Option 2: Awesome set-based approach, run a single SELECT such as: SELECT COUNT(*) FROM YourTable

So what I mean, is try to refactor your SQL to find a set-based approach to achieving the result you want. There are generally very few times where a loop/cursor approach can't be replaced by a set-based approach.

Upvotes: 3

dani herrera
dani herrera

Reputation: 51665

As you can see in Using nested cursors to produce report output microsoft example, you can work with nested cursors.

In sample vendor_cursor contains product_cursor.

Upvotes: 2

vulkanino
vulkanino

Reputation: 9134

You can have the cursors nested but you need to declare/open/fetch/close/deallocate the inner cursor within the WHILE loop of the outer cursor.

Upvotes: 1

Related Questions