Cursor status after declaring, opening, closing and deallocating a cursor

Posted by Bandi under Sql Server category on 3/31/2015 | Points: 40 | Views : 704

Post Code |

Search Codes |

Code Home

DECLARE @Temp TABLE 
(
   ii int
)


INSERT INTO @Temp(ii) VALUES(1)
INSERT INTO @Temp(ii) VALUES(2)
INSERT INTO @Temp(ii) VALUES(3)

--Create a cursor.
DECLARE cur CURSOR
FOR SELECT * FROM @Temp

--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
SELECT CURSOR_STATUS('global','cur') AS 'After Deallocate'

Output:


After declare
-1
After Open
1
After Close
-1
After Close
-3

Alert Moderator

Bookmark It

Comments or Responses

Posted by: Bandic on: 4/1/2015 Level:Starter | Status: [Member] | Points: 10

DECLARE test_cursor1 CURSOR FAST_FORWARD LOCAL FOR 
SELECT object_id
FROM sys.objects

select 'after declare' s, * from sys.dm_exec_cursors(null)

-- Cursor variables
DECLARE @object_id  INT

OPEN test_cursor1

select 'after open' s, * from sys.dm_exec_cursors(null)

FETCH NEXT FROM test_cursor1 INTO @object_id
WHILE @@fetch_status = 0
BEGIN

    -- cursor code here 
    -- ...

    FETCH NEXT FROM test_cursor1 INTO @object_id
END

CLOSE test_cursor1

select 'after close' s, * from sys.dm_exec_cursors(null)

DEALLOCATE test_cursor1

-- This won't show anything ...
select 'after deallocate' s, * from sys.dm_exec_cursors(null)

Latest Code Snipptes