CURSOR_STATUS Unreliable: SQL 2005 -- Is There A Better Way To Find Cursor Status?

What is the best way of reliably closing a cursor inside a BEGIN CATCH block? The simple problem statement is: I want to check if the cursor is open, then close it. I'm trying to use CURSOR_STATUS function and it seems to return a -3 (both when the cursor is open and not open.). Is this a bug or am I missing something?

I have removed all of my code and only provided what is necessary to repro the problem.

====================================================================================================

CREATE PROCEDURE dbo.repro_PROC

(

@condition1 varchar(10),

@condition2 varchar(10)

)

AS

BEGIN

BEGIN TRY


SET NOCOUNT ON;

SET XACT_ABORT ON;

DECLARE @dbName varchar(64), @dbid int;

DECLARE @FileID int;

DECLARE @Physical_Name varchar(512);

IF (@condition1 = 'ERROR')


RAISERROR ('ERROR: This error happens before any cursor is declared or is open', 16, 1);


DECLARE dbNameCursor CURSOR FOR SELECT Name, database_id FROM master.sys.databases ORDER BY Name;

OPEN dbNameCursor;


FETCH NEXT FROM dbNameCursor INTO @dbName, @dbid;

WHILE @@FETCH_STATUS = 0

BEGIN


DECLARE fileCursor CURSOR FOR SELECT File_ID, Physical_Name FROM master.sys.master_files

WHERE database_id = @dbid

ORDER BY File_ID;


OPEN fileCursor;

FETCH NEXT FROM fileCursor INTO @FileID, @Physical_Name;

WHILE @@FETCH_STATUS = 0

BEGIN


-- some condition which causes an exception

IF (@condition2 = 'ERROR')


RAISERROR ('ERROR: This error happens after both cursors are open', 16, 1);


FETCH NEXT FROM fileCursor INTO @FileID, @Physical_Name;

END

CLOSE fileCursor;

DEALLOCATE fileCursor;




FETCH NEXT FROM dbNameCursor INTO @dbName, @dbid;

END

CLOSE dbNameCursor;

DEALLOCATE dbNameCursor;

END TRY

BEGIN CATCH


DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

DECLARE @fileCursorStatus INT;

DECLARE @dbNameCursorStatus INT;


SELECT

@ErrorMessage = ERROR_MESSAGE(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE();


SET @fileCursorStatus = CURSOR_STATUS('local', 'fileCursor');

SET @dbNameCursorStatus = CURSOR_STATUS('local', 'dbNameCursor');

PRINT 'Cursor_Status (fileCursor) = ' + convert(varchar, @fileCursorStatus);

PRINT 'Cursor_Status (dbNameCursor) = ' + convert(varchar, @dbNameCursorStatus);


IF (@fileCursorStatus >= 0)

BEGIN


PRINT 'Closing fileCursor';

CLOSE fileCursor;

DEALLOCATE fileCursor;

END


IF (@dbNameCursorStatus >= 0)

BEGIN


PRINT 'Closing dbNameCursor';

CLOSE dbNameCursor;

DEALLOCATE dbNameCursor;

END


-- info about the original error

RAISERROR (@ErrorMessage, -- Message text.

@ErrorSeverity, -- Severity.

@ErrorState -- State.

);


END CATCH;

END;

====================================================================================================

EXEC dbo.repro_PROC @condition1='ERROR', @condition2='NO-ERROR'


Output:

Cursor_Status (fileCursor) = -3

Cursor_Status (dbNameCursor) = -3

Msg 50000, Level 16, State 1, Procedure repro_PROC, Line 88

ERROR: This error happens before any cursor is declared or is open

-------------------------------------------------------------------------

EXEC dbo.repro_PROC @condition1='NO-ERROR', @condition2='ERROR'


Output:

Cursor_Status (fileCursor) = -3

Cursor_Status (dbNameCursor) = -3

Msg 50000, Level 16, State 1, Procedure repro_PROC, Line 89

ERROR: This error happens after both cursors are open

-------------------------------------------------------------------------

EXEC dbo.repro_PROC @condition1='NO-ERROR', @condition2='ERROR'


Output:

Cursor_Status (fileCursor) = -3

Cursor_Status (dbNameCursor) = -3

Msg 50000, Level 16, State 1, Procedure repro_PROC, Line 89

A cursor with the name 'dbNameCursor' already exists. <=== THIS IS A PROBLEM

=================================================================================================


If I try to close the cursor when the status = -3, then sometimes it closes successfully, some other times, it errors "Cursor NOT open" etc.