Home

	Expand Search

Transact-SQL User's Guide

Chapter 17 Cursors: Accessing Data Row by Row

Fetching data rows using cursors

Fetching data rows using cursors

A fetch completes the cursor result set and returns one or more rows to the client that is responsible for extracting the column data from the row. Depending on the type of query defined in the cursor, Adaptive Server creates the cursor result set either by scanning the tables directly or by scanning a worktable generated by the query type.

The fetch command positions the cursor before the first row of the cursor result set. If the table has a valid index, Adaptive Server positions the cursor at the first index key.

Optionally, you can include Transact-SQL parameters or local variables with fetch to store column values.

fetch syntax

The syntax for the fetch statement is:

fetch cursor_name [into fetch_target_list]

After generating the cursor result set, Adaptive Server moves the cursor position one or more rows down the result set. It retrieves the data from each row of the result set and stores the current position, allowing additional fetches until Adaptive Server reaches the end of the result set.

For example, after declaring and opening the authors_crsr cursor, you can fetch the first row of its result set as follows:

fetch authors_crsr

au_id        au_lname            au_fname
-----------  ------------------- ---------------
341-22-1782  Smith              Meander
 
(1 row affected)

Each subsequent fetch retrieves another row from the cursor result set. For example:

fetch authors_crsr

au_id       au_lname            au_fname
----------- ------------------- ---------------
527-72-3246 Greene               Morningstar
 
(1 row affected)

After you fetch all the rows, the cursor points to the last row of the result set. If you fetch again, Adaptive Server returns a warning through the @@sqlstatus variable (described under "Checking the cursor status") indicating there is no more data. The cursor position remains unchanged.

You cannot fetch a row that has already been fetched. Close and reopen the cursor to generate the cursor result set again and start fetching from the beginning.

The into clause specifies that Adaptive Server returns column data into the specified variables. The fetch_target_list must consist of previously declared Transact-SQL parameters or local variables.

For example, after declaring the @name, @city, and @state variables, you can fetch rows from the pubs_crsr cursor as follows:

fetch pubs_crsr into @name, @city, @state

Adaptive Server expects a one-to-one correspondence between the variables in the fetch_target_list and the target list expressions specified by the select_statement that defines the cursor. The datatypes of the variables or parameters must be compatible with the datatypes of the columns in the cursor result set.

Checking the cursor status

Adaptive Server returns a status value after each fetch. You can access the value through the global variable @@sqlstatus. Table 17-1lists @@sqlstatus values and their meanings:

The following example determines the @@sqlstatus for the currently open authors_crsr cursor:

select @@sqlstatus

---------
        0
 
(1 row affected)

Only a fetch statement can set @@sqlstatus. Other statements have no effect on @@sqlstatus.

Getting multiple rows with each fetch

By default, fetch retrieves only one row at a time. You can use the cursor rows option of the set command to change the number of rows that are returned by fetch. However, this option does not affect a fetch containing an into clause.

The syntax for set is:

set cursor rows number for cursor_name

number specifies the number of rows for the cursor. The number can be a numeric literal with no decimal point, or a local variable of type integer. The default setting is 1 for each cursor you declare. You can set the cursor rows option for any cursor whether it is open or closed.

For example, you can change the number of rows fetched for the authors_crsr cursor as follows:

set cursor rows 3 for authors_crsr

After you set the number of cursor rows, each fetch of authors_crsr returns three rows from the cursor result set:

fetch authors_crsr

au_id       au_lname            au_fname 
----------- ------------------- --------------- 
648-92-1872 Blotchet-Halls      Reginald 
712-45-1867 del Castillo        Innes 
722-51-5424 DeFrance            Michel 
 
(3 rows affected)

The cursor is positioned on the last row fetched (the author Michel DeFrance in the example).

Fetching several rows at a time works especially well for client applications. If you fetch more than one row, Open Client or Embedded SQL buffers the rows sent to the client application. The client still sees a row-by-row access, but each fetch results in fewer calls to Adaptive Server, which improves performance.

Checking the number of rows fetched

Use the @@rowcount global variable to monitor the number of rows of the cursor result set returned to the client up to the last fetch. This variable displays the total number of rows seen by the cursor at any one time.

Once all the rows are read from a cursor result set, @@rowcount represents the total number of rows in that result set. Each open cursor is associated with a specific @@rowcount variable. The variable is dropped when you close the cursor. Checking @@rowcount after a fetch provides you with the number of rows read for the cursor specified in that fetch.

The following example determines the @@rowcount for the currently open authors_crsr cursor:

select @@rowcount

---------
        5
 
(1 row affected)