How to detect if a cursor has not been de-allocated or is still open?

Hi,

I am using try/catch block for my stored procedure (SQL Server 2005), when a cursor is used, how do I detect if the cursor has not been de-allocated or is still open?

Right now, I am using something such as

Begin Try

Begin Transaction

..................

-- mycursor will be declared and used here

....................

Commit Transaction

End Try

Begin Catch

Begin Try

Close mycursor

Deallocate mycursor

End Try

Begin Catch

--if mycursor was already closed/de-allocated,

End Catch

End Catch

I have a feeling that there must be a better way to do it.

Any suggestion?

Thanks.

Cathie

Actually this is not a bad way to go if you have to use global cursors (or really cursors at all, though that is another topic). If you aren't using global cursors (DECLARE CURSOR <name> LOCAL , or use a Variable SET @.cursor = CURSOR , or even set the database option:

ALTER DATABASE <databaseName>
SET CURSOR_DEFAULT GLOBAL

And your cursors will deallocate automatically when they lose scope.

|||

Hello,

Just query the results of the new dmf:

select * from sys.dm_exec_cursors(0) -- 0 being all sessions, otherwise pass in @.@.spid

Cheers,

Rob

|||

The following script might help you...

Read more on BOL about sp_cursor_list

Code Snippet

DECLARE My_Cursor CURSOR

FOR SELECT * FROM Sys.Objects

OPEN My_Cursor

FETCH NEXT FROM My_Cursor

DECLARE @.CursorName AS VARCHAR(100)

DECLARE @.Report CURSOR

EXEC master.dbo.sp_cursor_list @.cursor_return = @.Report OUTPUT,

@.cursor_scope = 3;

FETCH NEXT INTO @.CursorName from @.Report

WHILE (@.@.FETCH_STATUS <> -1)

BEGIN

IF @.CursorName = 'My_Cursor'

BEGIN

CLOSE My_Cursor

DEALLOCATE My_Cursor

END

FETCH NEXT INTO @.CursorName from @.Report

END

CLOSE @.Report

DEALLOCATE @.Report

sql