Home > SQL Server > SQL SERVER – SET CURSOR_CLOSE_ON_COMMIT @SQLSERVER

SQL SERVER – SET CURSOR_CLOSE_ON_COMMIT @SQLSERVER


SET CURSOR_CLOSE_ON_COMMIT : It specifies whether cursors close after the transaction opening the cursor has committed or rolled back.

When it is ON, the cursors that are open when a transaction is committed or rolled back are closed automatically.

When it is OFF, the cursors that are opened when a transaction is committed remain open and the rolled back transaction will close any cursors that are opened except those defined as INSENSITIVE or STATIC.

We can also set TURE or FALSE to CURSOR_CLOSE_ON_COMMIT enabled property in the database option permanently.

pic1SET CURSOR_CLOSE_ON_COMMIT Syntax

SET CURSOR_CLOSE_ON_COMMIT ON

or

SET CURSOR_CLOSE_ON_COMMIT OFF

Example

  • Execute this transact SQL statement with SET CURSOR_CLOSE_ON_COMMIT is ON

pic2Error has occurred while fetching the next row from the cursor after the transaction is committed. Because, the cursor is closed.

  • Execute transact SQL statement with SET CURSOR_CLOSE_ON_COMMIT is OFF

T-SQL

SET CURSOR_CLOSE_ON_COMMIT OFF
GO
begin  transaction
Declare cr cursor for select * from  tblIdentity
Open cr
commit transaction
fetch next from cr
close cr
deallocate cr

Output

pic3Now, the transact statement executed successfully.

I hope you have understood the use of SET CURSOR_CLOSE_ON_COMMIT set statement and its behavior when it is ON or OFF.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: