How to delete a cursor after return from procedure??

[kakmouth]

hi.

within a stored procedure, i created a cursor "Cursor1" but never deallocated that cursor before returning from the procedure. now if i attempt to execute that procedure again, i am told that "Cursor1" cannot be created because it already exists (in that database??). yet if i attempt to deallocate that cursor from the query analyzer, i am told that thje cursor DOES NOT exist.

where does the cursor now reside?? how might i go about deleting it??

thanks for your help. mike

 

[jhall156]

CLOSE <cursorname>

 

[balves]

CLOSE my_cursor
DEALLOCATE my_cursor

 

[kakmouth]

jhall,

now that i have returned from the stored procedure and CLOSED MY CONNECTION, SQL does not see that cursor any longer??? if i try to &quot;CLOSE <cursorname>&quot; it just tells me that it does not exist. yet if i try to create another cursor with the same name, the error message says that the cursor does exist.

any thoughts?? thanks again.

 

[jhall156]

From where are you executing the sp? Make sure that within your stored procedure you execute the following for your cursor to work properly.
DECLARE <cursorname> CURSOR <Intrinsic Constantx> FOR <query>

OPEN <cursorname>

<cursor operations and other code>

CLOSE <cursorname>
DEALLOCATE <cursorname>

If each of these executes then you should be fine in any connection.

 

[kakmouth]

yeah, i know to close and deallocate within the sp, it just turns out that i was doing some error checking and returned from the sp before doing the deallocation of the cursor. that cursor now exists somewhere, so i am unable to create a cursor with that name again.

where would this cursor actually be?? how can i &quot;deallocate&quot; it now that i have returned from the sp?

thanks.

 

[jhall156]

Once you close the connection all references to the cursor should be gone. If you open a new connection and still cannot declare and open the cursor then I'm stumped.