how to check if the cursor is already open

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to check if the cursor is already open

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-03-19 : 08:47:18

How to check if the normal cursor is open,

I want to close and deallocte the cursor if it is open.

If I write colse cur and deallocate cur, even it it is not opened
does it gives error.

I just want to check if the cursor is open if yes then I want to close and deallocate.

regards,
aak

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-19 : 08:55:48

You should really not use cursors, and if you do use them you should also know if it's open or closed. But if you really have to, use CURSOR_STATUS:

CREATE TABLE #TMP
(
   ii int
)
GO

INSERT INTO #TMP(ii) VALUES(1)
INSERT INTO #TMP(ii) VALUES(2)
INSERT INTO #TMP(ii) VALUES(3)

GO

--Create a cursor.
DECLARE cur CURSOR
FOR SELECT * FROM #TMP

--Display the status of the cursor before and after opening
--closing the cursor.

SELECT CURSOR_STATUS('global','cur') AS 'After declare'
OPEN cur
SELECT CURSOR_STATUS('global','cur') AS 'After Open'
CLOSE cur
SELECT CURSOR_STATUS('global','cur') AS 'After Close'

--Remove the cursor.
DEALLOCATE cur

--Drop the table.
DROP TABLE #TMP