Cursors

For more information about how SQL Server 6.5 affects existing database systems, see Cursors in the What's New for SQL Server 6.5.

Server cursors allow individual row operations to be performed on a given results set or on the entire set. In SQL Server 6.0, ANSI SQL cursors are server based. In earlier releases, cursors were provided only through the DB-Library and Open Database Connectivity (ODBC) cursor APIs.

The addition of cursors to the server provides an efficient way for single-row operations to occur within a given results set. ANSI SQL cursors support only single-row cursors. Each fetch will return only one row from the results set. Server cursors enhance the general use of relational databases by allowing row-level operations when set-oriented operations are impractical. In relational systems, set operations are preferred; cursors provide a powerful complement to set operations. Using cursors, multiple operations can be performed row by row against a results set with or without returning to the original table. In other words, cursors conceptually return a results set based on tables within the database(s). For example, a cursor can be generated to include a list of all user-defined table names within a database. After the cursor has been opened, movement (fetching) through the results set can include multiple operations against each table by passing each table name as a variable. Cursors are extremely powerful when combined with stored procedures and the EXECUTE statement (to dynamically build strings).

SQL Server provides two interfaces for cursor functions. When using cursors in Transact-SQL batches or stored procedures, ANSI-standard SQL syntax has been added for declaring, opening, and fetching from cursors as well as positioned updates and deletes. When using cursors from a DB-Library or ODBC program, the SQL Server 6.0 client libraries transparently call built-in server functions to handle cursors more efficiently.

This overview shows the statements, in order, used to implement a cursor.

In addition to browsing through the cursor results with FETCH, the current row of an open cursor can be modified. The UPDATE statement and the DELETE statement, when used against a cursor, affect only the row on which the cursor is currently positioned.

Syntax

    To update the current row:

UPDATE table_name
    SET column_name1 =
            {expression1 | NULL | (select_statement)}
        [, column_name2 =
            {expression2 | NULL | (select_statement)}...]
WHERE CURRENT OF cursor_name

    To delete the current row:

DELETE FROM table_name
WHERE CURRENT OF cursor_name

Modifications made with WHERE CURRENT OF cursor_name affect only the single row on which the cursor is positioned. In the case of a cursor based on a join, only the table_name specified in the DELETE or UPDATE statement is modified. Other tables participating in the cursor are not affected. For complete syntax and more information, see the DELETE and UPDATE statements.

The following sections discuss each of the statements used to define, access, and close a cursor.