Transact-SQL Cursors
Transact-SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts in which they make the contents of a result set available to other Transact-SQL statements.
The typical process for using a Transact-SQL cursor in a stored procedure or trigger is:
Declare Transact-SQL variables to contain the data returned by the cursor. Declare one variable for each result set column. Declare the variables to be large enough to hold the values returned by the column and with a data type that can be implicitly converted from the data type of the column.
Associate a Transact-SQL cursor with a SELECT statement using the DECLARE CURSOR statement. The DECLARE CURSOR statement also defines the characteristics of the cursor, such as the cursor name and whether the cursor is read-only or forward-only.
Use the OPEN statement to execute the SELECT statement and populate the cursor.
Use the FETCH INTO statement to fetch individual rows and have the data for each column moved into a specified variable. Other Transact-SQL statements can then reference those variables to access the fetched data values. Transact-SQL cursors do not support fetching blocks of rows.
When you are finished with the cursor, use the CLOSE statement. Closing a cursor frees some resources, such as the cursor's result set and its locks on the current row, but the cursor structure is still available for processing if you reissue an OPEN statement. Because the cursor is still present, you cannot reuse the cursor name at this point. The DEALLOCATE statement completely frees all resources allocated to the cursor, including the cursor name. After a cursor is deallocated, you must issue a DECLARE statement to rebuild the cursor.
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.
Plan Affecting Options |
ARITHABORT NUMERIC_ROUNDABORT FORCEPLAN QUOTED_IDENTIFIER ANSI_NULL_DFLT_ON ANSI_NULL_DFLT_OFF ANSI_WARNINGS ANSI_PADDING ANSI_NULLS CONCAT_NULL_YIELDS_NULL DATEFIRST DATEFORMAT LANGUAGE TEXTSIZE |
Indexed views and computed columns |
ANSI_NULLS ANSI_PADDING ANSI_WARNINGS ARITHABORT (under compatibility level of 80 or lower) CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIER NUMERIC_ROUNDABORT |
In SQL Server 2000, changes to ANSI_NULLS and QUOTED_IDENTIFIER did not raise an error, although the others did.