Transact-SQL Cursors

Monitoring Transact-SQL Cursor Activity

You can use the sp_cursor_list system stored procedure to get a list of cursors visible to the current connection, and sp_describe_cursor, sp_describe_cursor_columns, and sp_describe_cursor_tables to determine the characteristics of a cursor.

After the cursor is opened, the @@CURSOR_ROWS function or the cursor_rows column returned by sp_cursor_list or sp_describe_cursor indicates the number of rows in the cursor.

After each FETCH statement, @@FETCH_STATUS is updated to reflect the status of the last fetch. You can also get this status information from the fetch_status column returned by sp_describe_cursor. @@FETCH_STATUS reports conditions such as fetching beyond the first or last row in the cursor. @@FETCH_STATUS is global to your connection and is reset by each fetch on any cursor open for the connection. If you must know the status later, save @@FETCH_STATUS into a user variable before executing another statement on the connection. Even though the next statement may not be a FETCH, it could be an INSERT, UPDATE or DELETE that fires a trigger containing FETCH statements that reset @@FETCH_STATUS. The fetch_status column returned by sp_describe_cursor is specific to the cursor specified and is not affected by FETCH statements that reference other cursors. sp_describe_cursor is, however, affected by FETCH statements that reference the same cursor, so care is still needed in its use.

After a FETCH is completed, the cursor is positioned on the fetched row. The fetched row is known as the current row. If the cursor was not declared as a read-only cursor, you can execute an UPDATE or DELETE statement with a WHERE CURRENT OF cursor_name clause to modify the current row.

The name given to a Transact-SQL cursor by the DECLARE CURSOR statement can be either global or local. Global cursor names are referenced by any batch, stored procedure, or trigger executing on the same connection. Local cursor names cannot be referenced outside the batch, stored procedure, or trigger in which the cursor is declared. Local cursors in triggers and stored procedures are therefore protected from unintended references outside the stored procedure or trigger.

Using the cursor Variable

Microsoft SQL Server also supports variables with a cursor data type. A cursor can be associated with a cursor variable by either of two methods:

/* Use DECLARE @local_variable, DECLARE CURSOR and SET. */
DECLARE @MyVariable CURSOR;

DECLARE MyCursor CURSOR FOR
SELECT LastName FROM AdventureWorks2008R2.Person.Person;

SET @MyVariable = MyCursor;
GO
/* Use DECLARE @local_variable and SET */
DECLARE @MyVariable CURSOR;

SET @MyVariable = CURSOR SCROLL KEYSET FOR
SELECT LastName FROM AdventureWorks2008R2.Person.Person;
DEALLOCATE MyCursor;

After a cursor has been associated with a cursor variable, the cursor variable can be used instead of the cursor name in Transact-SQL cursor statements. Stored procedure output parameters can also be assigned a cursor data type and associated with a cursor. This allows stored procedures to expose local cursors in a controlled manner.

Referencing Transact-SQL Cursors

Transact-SQL cursor names and variables are referenced only by Transact-SQL statements; they cannot be referenced by the API functions of OLE DB, ODBC, and ADO. For example, if you use DECLARE CURSOR and OPEN a Transact-SQL cursor, there is no way to use the ODBC SQLFetch or SQLFetchScroll functions to fetch a row from the Transact-SQL cursor. Applications that need cursor processing and are using these APIs should use the cursor support built into the database API instead of Transact-SQL cursors.

You can use Transact-SQL cursors in applications by using FETCH and binding each column returned by the FETCH to a program variable. The Transact-SQL FETCH does not support batches, however, so this is the least efficient way to return data to an application. Fetching each row requires a roundtrip to the server. It is more efficient to use the cursor functionality built into the database APIs that support fetching batches of rows.

Transact-SQL cursors are extremely efficient when contained in stored procedures and triggers. This is because everything is compiled into one execution plan on the server and there is no network traffic associated with fetching rows.

Transact-SQL Cursors and SET Options

In SQL Server, an error is raised when a FETCH statement is issued in which there is a change in values as of the time the cursor was opened. This error occurs for any of the following plan-affecting options, or the options required for indexed views and computed columns. To avoid the error, do not change SET options while a cursor is open.

In SQL Server 2000, changes to ANSI_NULLS and QUOTED_IDENTIFIER did not raise an error, although the others did.

See Also

Reference

Concepts