fetch

Returns a row or a set of rows from a cursor result set.

Syntax

fetch [next | prior | first | last | absolute
	fetch_offset | relative fetch_offset] 
	[from] cursor_name
	[into fetch_target_list]

Parameters

Examples

Usage

You can fetch one or more rows at a time. To determine the number of rows fetched, use the cursor rows option of the set command, specifying the number of rows to fetch.

The value of @@rowcount is affected by whether the specified cursor is forward-only or scrollable. If the cursor is the default, nonscrollable cursor, the value of @@rowcount increments one by one, in the forward direction only, until the total number of rows in the result set are fetched.

Once all the rows have been read from the cursor result set, @@rowcount represents the total number of rows in the cursor results set. @@rowcount after a fetch to get the number of rows read for the cursor specified in that fetch.

If the cursor is scrollable, there is no maximum value for @@rowcount. For more information on @@rowcount, see Reference Manual: Building Blocks.

Cursor position:
  • For nonscrollable cursors, after you fetch all the rows, the cursor points to the last row of the result set. If you fetch again, the SAP ASE server returns a warning through the @@sqlstatus and @@fetch_status global variables, with value indicating there is no more data, and the cursor position moves beyond the end of the result set. You can no longer update or delete from that current cursor position.

  • With fetch into, the SAP ASE server does not advance the cursor position when an error occurs, because the number of variables in the fetch_target_list does not equal the number of target list expressions specified by the query that defines the cursor. However, it does advance the cursor position, even if a compatibility error occurs between the datatypes of the variables and the datatypes of the columns in the cursor result set.

Standards

ANSI SQL – Compliance level: Entry-level compliant.

The fetch of multiple rows is a Transact-SQL extension.

Permissions

fetch permission defaults to all users.

Related reference
declare cursor
open
set