meaning of close and deallocate cursor in stored procedure

Question

meaning of close and deallocate cursor in stored procedure

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
January 5, 2017 at 7:12 am

#339184

there is a condition that is using in Error block
IF @ID IN ('Employee Update','Employee Insert','Employee variable')
BEGIN
Close Empcursor
Deallocate Empcursor
END
What it meant by this above condition?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

It would appear the @ID contains certain user roles that are required to conditionally open a cursor somewhere else in the code. When an error occurs for other user roles that didn't open the cursor, the code to close and deallocate a cursor would fail because the cursor isn't open. So it's an offhand test to see if the cursor is open and then to close it if it is.

I damned sure wouldn't do it this way. I'd test the cursor directly to see if it were open. Can't answer how because I don't use cursors except to show how bad they are. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125562 More actions · Answer 2

I try to minimize the amount of complexity within the CATCH block of an error handler. According to MSDN there is no technical reason to explicitly close or deallocate a locally declared cursor, it will be deallocated when the procedure exits and the cursor falls out of scope. However, attempting to close a cursor that isn't open or deallocate a cursor that hasn't yet been declared will raise yet another error.

.. A cursor variable does not have to be explicitly deallocated. The variable is implicitly deallocated when it goes out of scope. ..

https://msdn.microsoft.com/en-us/library/ms188782.aspx

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Eric M Russell (1/5/2017)
I try to minimize the amount of complexity within the CATCH block of an error handler. According to MSDN there is no technical reason to explicitly close or deallocate a locally declared cursor, it will be deallocated when the procedure exits and the cursor falls out of scope. However, attempting to close a cursor that isn't open or deallocate a cursor that hasn't yet been declared will raise yet another error.
.. A cursor variable does not have to be explicitly deallocated. The variable is implicitly deallocated when it goes out of scope. ..
https://msdn.microsoft.com/en-us/library/ms188782.aspx

I wonder if that's still true when some form of connection pooling is in place.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)