DECLARE CURSOR Statement (version 6.5)

Allows individual row operations to be performed on a given results set. There are several cursor improvements in SQL Server 6.5:

• Dynamic cursors allow updates on underlying tables without requiring a unique index on the table.
• Forward-only cursors are significantly faster on large tables.
• Cursors automatically close upon COMMIT when SET CURSOR_CLOSE_ON_COMMIT option is set to ON.
• Forward-only cursors are dynamic by default, which allows faster cursor opening and also allows the results set to display updates made to the underlying tables.
• The results sets from read-only dynamic cursors no longer use temporary tables in tempdb.

DECLARE CURSOR cannot be used on a table that was created in the same batch or stored procedure.

If you need to use these statements in the same batch or stored procedure, you must put the DECLARE CURSOR statement inside a dynamic EXECUTE. Using a dynamic EXECUTE enables the cursor to benefit from any indexes on the temporary table, which allows a sensitive scrollable cursor.

An exception to this is when the created table is a temporary table (indicated by a leading number sign (#)). You can create a temporary table and declare a cursor on it in the same batch or stored procedure. However, the cursor will not recognize any of the table's indexes. Scroll cursors and any cursor with an ORDER BY statement will be created as INSENSITIVE and READ ONLY. Forward-only cursors (those that do not require an interim work table) will be dynamic and can be updated.

Note If the definition of the forward-only cursor contains interim work tables for outer joins or aggregate functions, then the forward-only cursor does not default to a dynamic cursor.

Example

This example illustrates the dynamic behavior of a forward-only cursor. There are two processes: User One declares and opens a cursor, then User Two makes a change to the underlying table, which affects the first row of the cursor. Finally, the update is displayed when User One fetches the first row.

These are the connections that illustrate the dynamic behavior of a forward-only cursor.

DECLARE forwardcursor CURSOR FOR
SELECT au_lname FROM authors
OPEN forwardcursor

UPDATE authors 
SET au_lname = 'Whittacker'
WHERE au_lname = 'White'

FETCH NEXT FROM forwardcursor

This is the results set for User One:

--------------------------------
au_lname
--------------------------------
Whittacker

(1 row(s) affected)