OPEN Statement

Opens a cursor.

Syntax

OPEN cursor_name

where

cursor_name: Specifies the name of an already-declared cursor. In the case of an INSENSITIVE cursor, this will generate the required temporary table. At the time the cursor is opened, results set membership and ordering are fixed.
Note Membership and ordering are fixed when the cursor is opened; updates and deletes that have been committed against the base tables of the cursor by other users will be reflected in fetches made against all cursors defined without the INSENSITIVE option. This behavior is commonly called keyset-driven. Rows that have been deleted from a results set will appear with NULLs in all variable columns, spaces in all char columns, and default or zero values in all other non-null columns. To differentiate between a deleted row and a row that has been inserted with NULL data values, use the global variable @@FETCH_STATUS after each FETCH is made against the cursor.

Remarks

Once a cursor has been opened, use the global variable @@CURSOR_ROWS to receive the number of qualifying rows in the last opened cursor. Depending on the number of rows expected in the results set, SQL Server may choose to populate the keyset asynchronously on a separate thread. This allows fetches to proceed immediately, even if the keyset is not fully populated. @@CURSOR_ROWS returns:

-m	If the cursor is being populated asynchronously. The value returned (-m) refers to the number of rows currently in the keyset.
n	If the cursor is fully populated. The value returned (n) refers to the number of rows.
0	If no cursors have been opened or the last opened cursor has been closed or deallocated.

To set the threshold at which SQL Server will generate keysets asynchronously, use the cursor threshold configuration option. For details, see the sp_configure system stored procedure.