How to check if cursor exists open status

Question

How do I check if a cursor is open or not  Because many times I am encountering the error  Cursor already exists   Please let me know how can I check whether a cursor is already in open status   In fact I have closed as well as Deallocated it at the end  CLOSE ppm cursor  DEALLOCATE ppm cursor   But Still i am getting the same error what could be the reason

User · Answer

Close the cursor  if it is empty then deallocate it   IF CURSOR STATUS  global   myCursor    gt   -1  BEGIN   IF CURSOR STATUS  global   myCursor    gt  -1    BEGIN     CLOSE myCursor    END  DEALLOCATE myCursor END

User · Answer

I rarely employ cursors  but I just discovered one other item that can bite you here  the scope of the cursor name     If the database CURSOR DEFAULT is global  you will get the  cursor already exists  error if you declare a cursor in a stored procedure with a particular name  eg  cur    and while that cursor is open you call another stored procedure which declares and opens a cursor with the same name  eg  cur     The error will occur in the nested stored procedure when it attempts to open  cur    Run this bit of sql to see your CURSOR DEFAULT   select is local cursor default from sys databases where name     your database name     If this value is  0  then how you name your nested cursor matters

User · Answer

You can use the CURSOR STATUS function to determine its state   IF CURSOR STATUS  global   myCursor   gt  -1 BEGIN  DEALLOCATE myCursor END

User · Answer

Just Small change to what Gary W mentioned  adding  SELECT    IF  SELECT CURSOR STATUS  global   myCursor     gt   -1 BEGIN  DEALLOCATE myCursor END   http   social msdn microsoft com Forums en sqlgetstarted thread eb268010-75fd-4c04-9fe8-0bc33ccf9357

User · Answer

This happened to me when a stored procedure running in SSMS encountered an error  during the loop  while the cursor was in use to iterate over records and before the it was closed   To fix it I added extra code in the CATCH block to close the cursor if it is still open  using CURSOR STATUS as other answers here suggest

[sql] How to check if cursor exists (open status)

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql

Examples related to sql-server-2008