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


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


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.


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”.


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.


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



Cursor position

Determining the number of rows fetched

Getting information about fetches

Using scrollable cursors


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


ANSI SQL – Compliance level: Entry-level compliant.

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


fetch permission defaults to all users.

See also

Commands declare cursor, open, set