How to Check Cursor Status in SQL

When we use cursor inside a try catch catch, if any exception occurs after opened the cursor, the cursor wont get closed. So when we execute the sql next time, we will get 'cursor already exist' exception.
We have to make sure that the cursor is closed and deallocated during the exception.

Following T-SQL gives the syntax.

CURSOR_STATUS
(
{ 'local','cursorname' }
| { 'global','cursorname' }
)

Where local, global are constant and indicates the type of cursor


DECLARE curTest CURSOR FOR SELECT * FROM table
OPEN curTest

0,1 - Cursor opened (0 is not applicable for dynamic cursor)
-1 - Cursor deallocated

if (CURSOR_STATUS('local',curTest)<0)
BEGIN
CLOSE curTest
DEALLOCATE curTest
END