fetch

Description

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. See “Rules for positioning the scrollable cursor”.

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


Restrictions


Cursor position


Determining the number of rows fetched


Getting information about fetches


Using scrollable cursors

fetch_direction:

The row number specified in the result set is counted from 1; the first row is number 1.


Multiple rows per fetch

Each fetch returns one row to the client in default behavior. The returned rows per fetch can be changed to another number by entering:

set cursor rows number for cursor_name

number specifies the number of rows per fetch the cursor can execute. This number can be a numeric literal with no decimal point, or a local variable of type integer. If cursor rows is greater than one, multiple rows return to the client after fetch. In some cases, the rows returned by fetch may be less than the number of rows specified, depending on the cursor’s position. The current cursor position is always one row.


Terms used in rules for positioning the scrollable cursor

These terms are used in “Rules for positioning the scrollable cursor”.


Rules for positioning the scrollable cursor

These rules govern the position of the cursor in fetch_orientation options when you are fetching the cursor rows, where cursPos is the cursor position. See the fetch_orientation options syntax:


Fetch first

The new_CurRowsetStart always moves to first_row, regardless of the position of CurRowsetStart and the value of fetchSize.


Fetch last


Fetch next


Fetch prior


Fetch relative


Fetch absolute

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.

See also

Commands declare cursor, open, set