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.

Answer 1

You can use the CURSOR_STATUS function to determine its state.

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