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

next | prior | first | last | absolute | relative – are keywords that specify the fetch direction. You do not need to specify the fetch direction for nonscrollable cursors. If you specify the fetch direction, you can use any of the other options to access the rows from a scrollable cursor. You must specify the fetch_offset when you use absolute or relative.
[from] cursor_name – is the name of the cursor. from is optional.
fetch_offset – specifies the offset value from a specific position. fetch_offset is required when you specify absolute or relative. fetch_offset can be either signed numeral literal with scale of zero, or Transact-SQL variable with a type of integer or numeric with a zero-scale numeral.
fetch_target_list – is a comma-separated list of parameters or local variables into which cursor results are placed. The parameters and variables must be declared prior to the fetch.

Examples

Example 1 – Returns a row of information from the cursor result set defined by the authors_crsr cursor:
```
fetch authors_crsr
```
Example 2 – Returns a row of information from the cursor result set defined by the pubs_crsr cursor into the variables @name, @city, and @state:
```
fetch pubs_crsr into @name, @city, @state
```
Example 3 – With scrollable cursors, you can use numeric literal offset with orientation keyword absolute. In this example, the 25th row is specified. Enter:
```
fetch absolute 25 from pubs_crsr 
    into @name, @city, @state
```
Example 4 – To use a Transact-SQL variable representing the 25th row, enter:
```
declare @offset int
select @offset = 25
fetch absolute @offset from c1
```

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.