SQL - Cursor close on commit

-------------------------------------------------------------------------------

-- SET CURSOR_CLOSE_ON_COMMIT

-------------------------------------------------------------------------------

SET NOCOUNT ON

-- Create a testing table

IF OBJECT_ID('CursorT1') IS NOT NULL

DROP TABLE CursorT1

GO

CREATE TABLE CursorT1 (

   a INT

)

GO

-- Insert testing records

INSERT INTO CursorT1 VALUES (1)

INSERT INTO CursorT1 VALUES (2)

GO

-- Cursor close on commit on

PRINT '-- SET CURSOR_CLOSE_ON_COMMIT ON'

GO

SET CURSOR_CLOSE_ON_COMMIT ON

GO

PRINT '-- BEGIN TRAN'

-- Begin a trasaction

BEGIN TRAN

-- Declare a cursor

PRINT '-- Declare and open cursor'

DECLARE testcursor CURSOR FOR

SELECT a FROM CursorT1

-- Open a cursor

OPEN testcursor

PRINT '-- Commit tran'

-- Commit a trasaction

COMMIT TRAN

PRINT '-- Try to use cursor'

FETCH NEXT FROM testcursor

-- Close and deallocate

CLOSE testcursor

DEALLOCATE testcursor

GO

-- Off commit

PRINT '-- SET CURSOR_CLOSE_ON_COMMIT OFF'

GO

SET CURSOR_CLOSE_ON_COMMIT OFF

GO

-- Begin a trasaction again

PRINT '-- BEGIN TRAN'

BEGIN TRAN

-- A new cursor

PRINT '-- Declare and open cursor'

DECLARE testcursor CURSOR FOR

SELECT a

FROM CursorT1

-- Open

OPEN testcursor

PRINT '-- Commit tran'

-- Commit, but it couldn't impact cursor

COMMIT TRAN

PRINT '-- Try to use cursor'

FETCH NEXT FROM testcursor

-- Close and deallocate

CLOSE testcursor

DEALLOCATE testcursor

GO

DROP TABLE CursorT1;

GO

SQL - How to use cursor

---------------------------------------------------------------------------------------------

--The following example uses @@FETCH_STATUS to control cursor activities in a WHILE loop.

--@@FETCH_STATUS returns the status of the last cursor FETCH statement issued against any cursor

--currently opened by the connection.

--  0 The Fetch statement was successful.

-- -1 The Fetch statement failed or the row was beyond the result set.

-- -2 The row fetched is missing.

---------------------------------------------------------------------------------------------

-- Declare a cursor first.

DECLARE Employee_Cursor CURSOR FOR -- For which dataset

SELECT TOP 10 BusinessEntityID,

       JobTitle

FROM AdventureWorks2008R2.HumanResources.Employee;

-- Need to open this cursor

OPEN Employee_Cursor;

-- Fetch next row from dataset

FETCH NEXT FROM Employee_Cursor;

-- Fetch status is 0, means fetched record successfully

WHILE @@FETCH_STATUS = 0

   BEGIN

-- Fetch next record from dataset

      FETCH NEXT FROM Employee_Cursor;

     

      -- We can see the last record shows the fetch status - -1

      PRINT 'Fetch Status - '+ CONVERT(VARCHAR(10),@@FETCH_STATUS)

   END;

-- Close the cursor

CLOSE Employee_Cursor;

-- Must remove this cursor reference

DEALLOCATE Employee_Cursor;

GO

SQL - Output cursor in stored procedure

USE AdventureWorks2008R2;

GO

-- Create a stored procedure

IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL

DROP PROCEDURE dbo.uspCurrencyCursor;

GO

-- If the parameter is CURSOR type, it must be assigned with VARYING and OUTPUT

-- If the parameter is assigned with VARYING, the data type must be cursor with OUTPUT

CREATE PROCEDURE dbo.uspCurrencyCursor

@CurrencyCursor CURSOR VARYING OUTPUT

AS

BEGIN

SET NOCOUNT ON;

-- Set cursor value

SET @CurrencyCursor = CURSOR

-- Only forward to use fetch next to fetch records from the first row to last row.

-- Static, fetch records from tempdb, just a copy from original records, can't update.

FORWARD_ONLY STATIC FOR

SELECT CurrencyCode,

Name

FROM Sales.Currency;

OPEN @CurrencyCursor;

END

GO

-- Declare a local cursor first

DECLARE @LocalCursor CURSOR

-- Get the cursor from usp

EXECUTE dbo.uspCurrencyCursor @CurrencyCursor = @LocalCursor OUTPUT

FETCH NEXT FROM @LocalCursor

WHILE @@FETCH_STATUS = 0

BEGIN

-- Fetch next row

FETCH NEXT FROM @LocalCursor

END

-- Close cursor and deallocate it

CLOSE @LocalCursor

DEALLOCATE @LocalCursor