What you need: SQL Server 7.0

These resultsets often include more than one row of data. Cursors provide a means for navigating through and manipulating the individual rows of a resultset. However, many developers find the many different types of cursors confusing because they're all similar in concept, but differ in capabilities and options. You can divide the different types of cursors into three primary groups: client cursors, API server cursors, and Transact-SQL (T-SQL) cursors. In this article, I'll focus on T-SQL cursors, explaining how they work and when to use them (see Resources for information about API server cursors).

You typically use T-SQL cursors from within stored procedures, triggers, or SQL scripts, where the referenced resultset is made available to other stored procedures, triggers, or SQL scripts. It's generally better to use an API server cursor than a T-SQL cursor if a client app needs to reference the resultset.

I'll begin by examining a stored procedure that illustrates the syntax for declaring and opening a cursor, scrolling through its resultset, and closing the cursor (see Listing 1). This procedure selects a title randomly from the Titles table in the Pubs database. The calling application features this title as the highlighted book of the day.

Do this by using the Rand function to select a random record in the Titles table. Then declare the cursor and populate it with the title_id, title, and notes columns from the Titles table. After opening the cursor and scrolling to the randomly selected record, populate the output parameters with the appropriate data for the randomly selected record. Finally, close and deallocate the cursor. If you need to reuse the same cursor again later on the same active connection, you might close it without deallocating it. However, it's important to deallocate cursors as soon as possible because they consume memory in the procedure cache. This memory isn't released until the cursor is deallocated. Implementing the functionality performed by this stored procedure without using cursors would be difficult unless you pass the entire resultset back the client application, which would then select a random title. Using a cursor lets you pass only the data for the necessary record across the network.

You can specify several options when you declare a cursor; these options control the cursor type and behavior (see Table 1). Use this extended T-SQL syntax to declare a cursor:

DECLARE my_cursor CURSOR
[LOCAL | GLOBAL]
[FORWARD ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR SQL_statement 
[FOR UPDATE [OF update_columns]]

Note that cursors are global by default. This means you can reference a cursor outside the stored procedure or batch you declare it in. However, cursors are valid only within the connection you declare them in. If you declare a cursor as a local cursor, it is available only within the scope of the stored procedure or batch you declare it in.

Sort Through Your Options
Like API server cursors, T-SQL cursor behavior and sensitivity to change is governed largely by the use of a Static, Keyset, or Dynamic argument. Static cursors (also referred to as snapshot cursors) are read-only. Moving or scrolling through static cursors consumes relatively few system resources. However, static cursors cannot detect any changes that might have been made to the data after the cursor has been opened. Another disadvantage to static cursors: They store the entire cursor in the tempdb. This can cause trouble if the cursor contains a fairly large resultset.

Unlike static cursors, keyset cursors store only the key value for the records in the tempdb's resultset. This takes up less space in the tempdb and gives the cursor access to changes that might have been made to the data after the cursor was opened. However, the key values are stored in the tempdb, so the records that make up the resultset of a keyset cursor cannot change unless the cursor is closed and reopened. In other words, new or modified records that now meet the cursor's criteria won't be visible to the cursor. Similarly, a record remains a cursor member even if it's deleted from the database.

If you try to scroll to a deleted record, you receive null values, zeros, and blank spaces in place of the actual data. Keyset cursors store the key value for each record in the tempdb, so you can open a keyset cursor only on a table with a unique index. If you try to open a keyset cursor on a table without a unique index, you won't receive an error message unless you declare the cursor using the Type_Warning argument. Instead, the cursor is implicitly changed to a static cursor. Unlike static and keyset cursors, the entire resultset for a dynamic cursor is refreshed every time a Fetch operation is executed on the cursor. This enables the cursor to see any changes that might have been made on the data by any user.

You navigate or scroll through T-SQL cursors with the Fetch statement. This statement enables you to specify the First, Last, Next, and Prior options from T-SQL, as well as the Absolute and Relative keywords. The Absolute keyword lets you navigate directly to a specified record number in the resultset (see Listing 1). However, you can't use the Absolute keyword with dynamic cursors because it's possible for records to be added or deleted from the resultset. Nevertheless, dynamic cursors do support the use of the Relative keyword. The Relative option navigates to the resultset record that is a specified number of records after the currently selected record.

You should also be aware of the @@Fetch_Status function when scrolling through cursors. This function's return value indicates the status of the last cursor Fetch statement executed against the active connection. @@Fetch_Status has three possible return values: 0, -1, or -2 (see Table 2). A return value of 0 indicates that the last executed Fetch statement completed successfully. This pseudocode shows you how to use the @@Fetch_Status function to loop through a cursor:

WHILE @@FETCH_STATUS = 0
BEGIN
   FETCH NEXT From my_cursor
END