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

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.

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

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!

Close the cursor, if it is empty then deallocate it:

IF CURSOR_STATUS('global','myCursor') >= -1
 BEGIN
  IF CURSOR_STATUS('global','myCursor') > -1
   BEGIN
    CLOSE myCursor
   END
 DEALLOCATE myCursor
END

Just Small change to what Gary W mentioned, adding 'SELECT':

IF (SELECT CURSOR_STATUS('global','myCursor')) >= -1
BEGIN
 DEALLOCATE myCursor
END

http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/eb268010-75fd-4c04-9fe8-0bc33ccf9357