x

how to close or deallocate cursor when it is in open state

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

asked Jan 04, 2011 at 05:47 AM in Default

Murali gravatar image

Murali
906 99 115 120

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If you use a cursor variable instead of a named cursor, then it will fall out of scope when you exit the procedure...

DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FOR
SELECT * FROM Person.Person;

OPEN @MyCursor

...

CLOSE @MyCursor
DEALLOCATE @MyCursor
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.
more ▼

answered Jan 04, 2011 at 05:51 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

that means ...if we use a cursor variable...if the procedure terminates in middle ...again same procedure executes...it will not say cursor is already open....
Jan 04, 2011 at 06:06 AM Murali
Matt ...is my understanding correct?
Jan 04, 2011 at 06:17 AM Murali
You understand correctly, since the variable falls out of scope, the cursor is implicitly deallocated. Using CURSOR LOCAL will have the same effect.
Jan 04, 2011 at 06:20 AM Magnus Ahlkvist
thank you very much
Jan 04, 2011 at 06:28 AM Murali
(comments are locked)
10|1200 characters needed characters left

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.

DECLARE cur CURSOR FAST_FORWARD LOCAL FOR
SELECT something FROM someone
OPEN CUR
..

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][1]

[1]: http://msdn.microsoft.com/en-us/library/ms180169.aspx
more ▼

answered Jan 04, 2011 at 06:11 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.5k 16 19 33

+1 - good extra info
Jan 04, 2011 at 07:11 AM Matt Whitfield ♦♦
(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:

x986
x58

asked: Jan 04, 2011 at 05:47 AM

Seen: 7111 times

Last Updated: Jan 04, 2011 at 06:04 AM