Using Cursors

When you write code for a transaction that retrieves a single row of results, you can use a SELECT INTO statement. This is called a singleton SELECT statement.

When you write code for a transaction where the result set includes several rows of data, you must declare and use a cursor. A cursor is a mechanism you can use to fetch rows one at a time. For example, if you write code that includes a SELECT statement or stored procedure that returns multiple rows, you must declare a cursor and associate it with the SELECT statement. Then, by using the FETCH statement, you can retrieve one row at a time from the result set.

You can also use cursors to perform operations within a result set. These operations are known as positioned update and positioned delete. For more information, see Positioned UPDATE or DELETE Statements.

Important  The cursor options available with Embedded SQL for C (ESQL/C) are different from the cursor options available with the Transact-SQL DECLARE CURSOR option, and the two should not be intermixed. If a Transact-SQL cursor is not available as an ESQL/C cursor option, it cannot be used.

Do not use ESQL/C cursors to process Transact-SQL batches or other operations that return multiple result sets. If a statement returns multiple result sets, only the first result set is recognized and subsequent result sets are discarded. If COMPUTE rows are returned, the rows are also ignored.

ESQL/C includes standard and browse cursor types. A standard cursor is used to retrieve one row of data at a time and shares the same connection to Microsoft® SQL Server™ as the main program. Standard cursors require a unique index in SQL Server version 6.0 and earlier. To set standard cursors, use the SET CURSORTYPE CUR_STANDARD statement or the DECLARE CURSOR statement with the FOR UPDATE option. A browse cursor is used to retrieve one row of data at a time and requires a separate connection to SQL Server. To set browse cursors, use the SET CURSORTYPE CUR_BROWSE statement.

Standard and browse cursors are declared and used (including FETCH and positioned update or delete operations) in the same way. Standard cursors are based on the DB-Library cursor model and allow multiple cursor operations to share the same connection to SQL Server. Each browse cursor requires a separate connection. For most applications, standard cursors are recommended and are the default because a shared single connection avoids potential locking conflicts between cursors.

Standard DB-Library cursors provides detailed descriptions of standard DB-Library cursors. DB-Library cursors have several options for controlling row membership, locking, and performance characteristics. These options are available to ESQL/C programs through the SET ANSI_DEFAULTS, SET CURSOR_CLOSE_ON_COMMIT, SET SCROLLOPTION, SET CONCURRENCY, and SET FETCHBUFFER statements. A SET option remains in effect for all cursor operations within an ESQL/C program until that option is changed by another SET statement.

Browse Cursors provides details about browse cursors. If positioned update or delete statements are used on a browse cursor, the SELECT statement used in the cursor declaration must include the FOR BROWSE option. However, because each browse cursor uses a separate database connection, SQL Server treats each cursor as a separate user. This can result in locking conflicts between different cursors in the same program. For more information, see Cursors and Lock Conflicts.

See Also

Standard DB-Library Cursors

Browse Cursors