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

avatar image

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

 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

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.

 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

more ▼

answered Jan 04, 2011 at 06:11 AM

avatar image

Magnus Ahlkvist
21.4k 19 39 42

  • 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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1069
x68

asked: Jan 04, 2011 at 05:47 AM

Seen: 11497 times

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

Copyright 2016 Redgate Software. Privacy Policy