declare cursor

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.

The select_statement is the query that defines the cursor result set. In general, select_statement can use nearly 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.

Examples of the declare cursor Command

The following declare cursor statement defines a result set for the authors_crsr cursor that contains all authors who 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 following example defines an insensitive scrollable result set, of the stores_scrollcrsr, containing bookstores in California, for:

declare storinfo_crsr insensitive scroll cursor
for select stor_id, stor_name, payterms
from stores
where state = "CA"

To declare an insensitive, nonscrollable cursor called “C1,” enter:

declare C1 insensitive cursor for
select fname from emp_tab

To declare an insensitive, scrollable cursor called “C3,”enter:

declare C3 insensitive scroll cursor for
select fname from emp_tab

To fetch the first row, enter:

fetch first from <cursor_name>

You can also fetch the columns of the first row from the result set. To place them in the variables you specify in <fetch_target_list>, enter:

fetch first from <cursor_name> into <fetch_target_list>

You can fetch the 20th row in the result set directly, regardless of the cursor’s current position:

fetch absolute 20 from <cursor_name> into <fetch_target_list>

cursor_scrollability
You can use either scroll or no scroll to specify cursor_scrollability. If the cursor is scrollable, you can scroll through the cursor result set by fetching any, or many rows back and forth; you can also repeatedly scan the result set.
Cursor Sensitivity
You can use either insensitive or semi_sensitive to explicitly specify cursor sensitivity.
read_only Option
The 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 that is defined as updatable.

Parent topic: Cursors: Accessing Data