Declares a cursor, which is a temporary worktable created from a result set for further processing.

Cursors allow row-by-row processing of result sets. Transact-SQL cursors are implemented using a Transact-SQL language extension that supports programmatic control of the cursor.

Syntax

DECLARE cursor_name [ CURSOR ] 
    [ LOCAL | GLOBAL ]
    [ FORWARD_ONLY ]
    [ READ_ONLY ]
    [ SCROLL ]
    [ STATIC ] 
    [ KEYSET ] 
    [ DYNAMIC ] 
    [ INSENSITIVE ] 
    [  ]
    [ FOR  ] 
    [ FOR UPDATE [ OF  [ ,...n ] ] ] 
    [;]

-- scroll_option ::= {
--     SCROLL_UP | SCROLL_DOWN | SCROLL_LEFT | SCROLL_RIGHT 
-- }

-- query_specification ::= {
--     select_statement | table_definition | stored_procedure | query_statement
-- }

Arguments

cursor_name

The cursor name. Cursor names must follow the rules for identifiers. If a cursor name is supplied for a cursor declared with the CURSOR keyword, the cursor name and the cursor are the same. If no cursor name is supplied, the cursor is an unnamed cursor.

LOCAL

Specifies that the cursor name is local to the batch, stored procedure, or function where the cursor is declared. The cursor name is only valid within that scope.

GLOBAL

Specifies that the cursor name is global to the stored procedure and the underlying session.

FORWARD_ONLY

Specifies that the cursor can only be scrolled forward. The cursor can only be opened, fetched forward, and closed. This option disables scroll functionality.

READ_ONLY

Specifies that no updates can be made through this cursor to the underlying table. The cursor is a read-only cursor.

SCROLL

Specifies that the cursor can be scrolled in both forward and backward directions. Depending on the cursor type, SCROLL might also enable fetching from the end of the result set.

STATIC

Creates a temporary copy of the data rows in the tempdb database that the cursor will use. This guarantees that the cursor always shows the results of the query as they existed when the cursor was opened, even if the underlying base tables are modified by other users. Changes made to the data in the underlying tables after the cursor is opened are not reflected in the data fetched from the cursor. STATIC cursors do not support positioning at the end of the result set.

KEYSET

Specifies that the cursor is sensitive to changes made by all users to the data in the underlying tables that affect the cursor rows. The cursor identifies rows by using unique key values from the set of rows that uniquely identify them. The keyset is built when the cursor is opened.

DYNAMIC

Specifies that the cursor is sensitive to all changes made by all users to the data in the underlying tables. The cursor reflects any changes to data in the rows that match the cursor's select list and WHERE clause.

INSENSITIVE

Creates a temporary copy of the data rows in the tempdb database that the cursor will use. This guarantees that the cursor always shows the results of the query as they existed when the cursor was opened. Changes made to the data in the underlying tables after the cursor is opened are not reflected in the data fetched from the cursor. INSENSITIVE cursors do not support positioning at the end of the result set. This is the default behavior when STATIC or INSENSITIVE is not specified and SCROLL is not specified.

Examples

A. Declaring a simple forward-only cursor 
DECLARE EmpCursor CURSOR FOR
SELECT EmployeeID, Name FROM HumanResources.Employee
ORDER BY EmployeeID;

OPEN EmpCursor;
FETCH NEXT FROM EmpCursor;
-- Process the fetched row
CLOSE EmpCursor;
DEALLOCATE EmpCursor;
B. Declaring a scrollable, read-only cursor 
DECLARE ProductCursor CURSOR FOR
SELECT ProductID, Name, ListPrice FROM Production.Product
ORDER BY ProductID;

OPEN ProductCursor;
FETCH LAST FROM ProductCursor; -- Move to the last row
FETCH PRIOR FROM ProductCursor; -- Move to the previous row
CLOSE ProductCursor;
DEALLOCATE ProductCursor;

See Also