Structured Query Language
SQL Tutorial and Tips

      Select | Insert | Update | Delete

      Open Cursor | Fetch

      Close Cursor | Union



        Structured Query Language
OPEN Cursor

Syntax:

OPEN cursor_name

where

cursor_name

Specifies the name of an already-declared cursor. At the time the cursor is opened, results set membership and ordering are fixed.

Note:

Following is specific for MS-SQL Server & SYBASE

In the case of an INSENSITIVE cursor, this will generate the required temporary table.

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.



Home         Business Directory

Festivals and Fairs (USA)