CODEWITHSUNDEEP
sql-serverstored-procedures
punkrockbuddyholly
punkrockbuddyholly

Reputation: 9794

Do you have to close a cursor in a T-SQL Stored Procedure?

We're getting intermittent problems with PayPal orders and I've been digging into our code. We're getting an exception which says "The wait operation timed out" in a particular stored procedure. In the stored procedure I can see that a cursor called item_cursor is declared and later opened but it is never closed. The stored procedure simply ends without ever calling close item_cursor (or DEALLOCATE item_cursor).

I'm a front-end dev rather than back-end so I'm a little out of my depth but could this be causing the problem?

Upvotes: 3

Views: 1855

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

It depends on how the cursor is declared:

LOCAL

Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is only valid within this scope. ... The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.

GLOBAL

Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect.

(My emphasis)

It then goes on to explain how it chooses a default if neither is specified, but if you've not changed anything else on your system, it will be GLOBAL by default, which in this case means that, yes, it will be "leaking". If the cursor is holding any locks it will continue to hold those locks until your connection is disconnected.

Upvotes: 6

Related Questions