in the store procedure i am using cursor...suppose if the store procedure has some problem..it is came out in-middle....without properly closing the cursor....second time the same store procedure if i execute..if will say that ...cursor is already opened....so...i need to check before opening the cursor..if the cursor is already open...then i need to close and deallocate it....
how to do that...
If you use a cursor variable instead of a named cursor, then it will fall out of scope when you exit the procedure...
However, happy as I am to answer your question, I feel that I really should point out that you should try to avoid using a cursor at all.
answered Jan 04, 2011 at 05:51 AM
Matt Whitfield ♦♦
I agree with Matts point - don't use cursors if you can avoid it. You probably CAN avoid it.
However, cursors exist, and sometimes they are handy. I use them sometimes, in maintenance tasks and sometimes when exporting hierarchical data to flatfiles.
I normally use a LOCAL cursor, and I try to find a cursor with as little overhead as possible.
If you declare the cursor as LOCAL, it will only exist in the stored procedure scope. The default is global.
If you only want to move forward in the cursor, and not use it for updates, you want to specify it as FAST_FORWARD as well, to avoid some of the overhead associated with cursors that allow scrolling and that are updateable.
This goes for CURSOR variables as well (except the LOCAL/GLOBAL since the variable falls out of scope anyway)
Here's the Books Online T-SQL reference for DECLARE CURSOR: http://msdn.microsoft.com/en-us/library/ms180169.aspx