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 results set includes several rows of data, you must declare and use a cursor. 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 results set. A cursor is a mechanism you can use to fetch rows one at a time.
You can also use cursors to perform operations within a results set. These operations are known as positioned update and positioned delete. For more information, see "Positioned Update or Delete Statements," later in this chapter.
Important
Do not use Embedded SQL cursors to process Transact-SQL batches or other operations that return multiple results sets. If a statement returns multiple results sets, only the first results set is recognized and subsequent results sets are discarded. If COMPUTE rows are returned, the rows are also ignored.
Embedded SQL includes standard cursor types and browse cursor types. A standard cursor is used to retrieve one row of data at a time and shares the same connection to 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 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 defaults because a shared single connection avoids potential locking conflicts between cursors. Standard cursors also use the cursor functions of DB-Library, so Embedded SQL applications that use standard cursors can automatically take advantage of any future performance enhancements in DB-Library cursors.
Appendix B, "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 Embedded SQL 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 Embedded SQL program until that option is changed by another SET statement.
Appendix C, "Browse Cursors," provides details about browse cursors. If positioned update or delete statements are to be used on a browse cursor, the SELECT statement that is 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 that are used in the same program. For more information, see Appendix C, "Cursors and Lock Conflicts."