CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlsql-server-2008
Maddy
Maddy

Reputation: 3816

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.

Upvotes: 55

Views: 170313

Answers (6)

user2787130
user2787130

Reputation: 21

Expanding on a previous answer, this proc is useful to call if you are worried that the cursor may have been left open or allocated

CREATE OR ALTER PROCEDURE dbo.CloseAndDeallocateCursor
    @cursorName NVARCHAR(80)
AS
BEGIN
    IF CURSOR_STATUS('global', @cursorName) >= -1
    BEGIN
        DECLARE @SQL NVARCHAR(91)
        IF CURSOR_STATUS('global', @cursorName) > -1
        BEGIN
            SET @SQL = N'CLOSE ' + @cursorName
            EXEC sp_executeSQL @SQL
        END
        SET @SQL = N'DEALLOCATE ' + @cursorName
        EXEC sp_executeSQL @SQL
    END
END
GO

... and then sample usage ...

EXEC dbo.CloseAndDeallocateCursor 'myCursor'
DECLARE myCursor STATIC 
        FOR SELECT * FROM blah

Upvotes: 1

Prateek
Prateek

Reputation: 1220

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

Upvotes: 53

Truisms Hounds
Truisms Hounds

Reputation: 441

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

Upvotes: 0

Tom Regan
Tom Regan

Reputation: 3841

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!

Upvotes: 0

Muhammad Omar ElShourbagy
Muhammad Omar ElShourbagy

Reputation: 7068

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

Upvotes: 7

Gary W
Gary W

Reputation: 1904

You can use the CURSOR_STATUS function to determine its state.

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

Upvotes: 108

Related Questions