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

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 372
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

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)

Login to post response