MS SQL Basics Tutorial: Turning off the release cursor

13.5.1 Close Cursor

1. Close the cursor with the closing command

After processing the data in the cursor, the cursor must be closed to release the data result set and the lock positioned on the data record. The close statement closes the cursor, but does not release the data structure that the cursor occupies. If you are ready to open the cursor again in subsequent use, you should use the Close command. The syntax rules for closing cursors are:

Close {{[GLOBAL] cursor_name} | cursor_variable_name}

2. Automatically close cursors

We have learned that cursors can be applied in stored procedures, triggers, and transact_sql scripts. If a transaction structure is used between the declaring cursor and the release cursor, the cursor is automatically closed when the transaction is closed. Its specific situation is as follows:

(1), declaring a cursor

(2), open cursor

(3), read cursor

(4), BEGIN transation

(5), Data processing

(6), COMMIT transation

(7), back to step 3

In such an application environment. When a data record is read from a cursor to begin with a begin transation, a COMMIT transation or rollback is an end transaction, the first row of data can be returned correctly after the program starts running, and the program returns to step 3 through step 7, Reads the next row of the cursor, which is often found to be an error message that is not open. The reason for this is that when a transaction ends, the MS SQL SERVER automatically closes the cursor, regardless of whether it ends with a commit transation or rollback transation, so the error is caused when the data is continued to be read from the cursor.

The way to resolve this error is to use the SET command to set the CURSOR_CLOSE_ON_COMMIT parameter to off state. The goal is to keep the cursor open at the end of the transaction without being closed. The format for using the SET command is:

SET Cursor_close_on_commit off

13.5.2 Release cursor

When using a cursor, various actions against the cursor either refer to the cursor name or reference a cursor variable that points to the cursor. When the close command closes the cursor, it does not release the data structure that the cursor occupies. Therefore, the deallocate command is often used. This command allows you to delete the connection between a cursor and a cursor name or a cursor variable, and to release all system resources that the cursor occupies. Its syntax rules are:

deallocate {{[GLOBAL] cursor_name} | @cursor_variable_name}

The meanings of each parameter refer to the 13.3 Open Cursors section.

When you use the deallocate @cursor_variable_name to delete a cursor, the cursor variable is not freed unless the stored procedure that uses the cursor, the scope of the trigger (that is, the cursor) is exceeded.

13.5.3 cursor Variable

A cursor variable is a new type of data that has been used since the MS SQL SERVER 7 version. There are two main methods of defining a cursor variable.

First we'll declare a cursor.

Assign a cursor to a cursor variable using the SET statement:

Place the declaration cursor sentence in the marker assignment statement, as follows:

Example 13-5: Here is a concrete and complete example in which we will have a clearer understanding of the deallocate command.

See the full set of "MS SQL Basics Tutorials"