Defining Cursors

Embedded SQL for C and SQL Server

Embedded SQL for C and SQL Server

Defining Cursors

Standard cursors can be used to scroll through and update a result set. Standard cursors do not require a unique index for Microsoft® SQL Server™ version 6.5 and later, do not require a timestamp or a second connection to a database for updates, and do not create a copy of the entire result set.

Note  Standard cursors will be enhanced in later versions; however, browse cursors will not.

These are the cursor types and their behavior:

Behavior Standard Browse
Connections to the server All share the same connection. One per cursor, plus one base connection for all updates.
timestamp column Not required. Required for positioned update or delete operations.
Transaction behavior All cursors share the same transaction space. Standard cursors cannot block each other. Read-only cursors can block updates made by the same program.
Unique index Not required for SQL Server version 6.5 and later. Required for update only.
Data currency Data values are always current. Row membership depends on the SCROLLOPTION setting:
  • If keyset-driven, membership is fixed at cursor-open time.

  • If dynamic, membership changes.
For read-only cursors, all data is current.

For updatable cursors, the server takes a snapshot of the data and stores it in the tempdb database when the cursor is opened.

Restrictions on use of SQL language Cannot be used on stored procedures.

Cannot contain UNION, GROUP BY, and HAVING clauses. In SQL Server version 6.5 and later, dynamic cursors can contain an ORDER BY clause if the table has a unique index.

Updatable cursors require the FOR BROWSE clause.