The syntax of the declare cursor statement is:
declare cursor_name cursor
for select_statement
[for {read only | update [of column_name_list]}]
The cursor_name is the name of the cursor. It must be a valid Adaptive Server identifier containing no more than 30 characters, and it must start with a letter, a pound sign (#), or an underscore (_).
The select_statement is the query that defines the cursor result set. See select in the Reference Manual for information about its options. In general, select_statement may use the full syntax and semantics of a Transact-SQL select statement, including the holdlock keyword. However, it cannot contain a compute, for browse, or into clause.
The for read only option specifies that the cursor result set cannot be updated. In contrast, the for update option specifies that the cursor result set is updatable. You can specify of column_name_list after for update with the list of columns from the select_statement defined as updatable.
The declare cursor statement must precede any open statement for that cursor. You cannot combine declare cursor with other statements in the same Transact-SQL batch, except when using a cursor in a stored procedure.
For example, the following declare cursor statement defines a result set for the authors_crsr cursor that contains all authors that do not reside in California:
declare authors_crsr cursor for select au_id, au_lname, au_fname from authors where state != "CA" for update
The select_statement can contain references to Transact-SQL parameter names or local variables. However, the names can reference only the parameters and local variables defined in a stored procedure that contains the declare cursor statement. If the cursor is used in a trigger, the select_statement can also reference the inserted and deleted temporary tables that are used in triggers. For information on using the select statement, see Chapter 2, “Queries: Selecting Data from a Table.”
