SET Cursor_close_on_commit - TSQL Tutorial

SET Cursor_close_on_commit - The default value for CURSOR_CLOSE_ON_COMMIT is OFF.
With CURSOR_CLOSE_ON_COMMIT set OFF the server will not close cursors when you commit a transaction.

SET Cursor_close_on_commit Syntax:

SET CURSOR_CLOSE_ON_COMMIT { ON | OFF } ;

SET Cursor_close_on_commit Example:

USE model;
GO
CREATE TABLE my_table (a INT, b CHAR(10));
GO
INSERT INTO my_table VALUES (1,'a'), (2,'b');
GO
SET CURSOR_CLOSE_ON_COMMIT OFF;
GO
PRINT 'BEGIN TRANSACTION';
BEGIN TRAN;
PRINT 'Declare cursor';
DECLARE my_cursor CURSOR FOR SELECT * FROM my_table;
PRINT 'Open cursor';
OPEN my_cursor;
PRINT 'COMMIT TRANSACTION';
COMMIT TRAN;
PRINT 'Use cursor after commit transaction';
FETCH NEXT FROM my_cursor;
CLOSE my_cursor;
DEALLOCATE my_cursor;
GO

Messages
BEGIN TRANSACTION
Declare cursor
Open cursor
COMMIT TRANSACTION
Use cursor after commit transaction