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

avatar image

906 109 119 123

(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...

 SELECT * FROM Person.Person;

 OPEN @MyCursor


 CLOSE @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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

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.

 SELECT something FROM someone

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

more ▼

answered Jan 04, 2011 at 06:11 AM

avatar image

Magnus Ahlkvist
22.5k 20 44 43

  • 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.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 04, 2011 at 05:47 AM

Seen: 13104 times

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

Copyright 2018 Redgate Software. Privacy Policy