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]
are keywords that specify the fetch direction. You do not need to specify the fetch direction for non-scrollable cursors. If you specify the fetch direction, you can use any of the othe roptions to access the rows from a scrollabe cursor. You must specify the fetch_offset when you use absolute or relative.
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” for more details.
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.
Returns a row of information from the cursor result set defined by the authors_crsr cursor:
fetch authors_crsr
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
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
To use a Transact-SQL variable representing the 25th row, enter:
declare @offset int select @offset = 25 fetch absolute @offset from c1
Before you can use fetch, you must declare the cursor and open it.
You can use fetch with an archive database.
The cursor_name cannot be a Transact-SQL parameter or local variable.
For nonscrollable cursors, you cannot fetch a row that has already been fetched. There is no way to backtrack through the result set, but you can close and reopen the cursor to create the cursor result set again and start from the beginning.
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.
When you set chained transaction mode, Adaptive Server implicitly begins a transaction with the fetch statement if no transaction is currently active. However, this situation occurs only when you set the close on endtran option and the cursor remains open after the end of the transaction that initially opened it, since the open statement also automatically begins a transaction.
For nonscrollable cursors, 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 and @@fetch_status global variables, with value that indicate 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, Adaptive 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.
You can fetch one or more rows at a time. Use the cursor rows option of the set command to specify the number of rows to fetch.
The @@sqlstatus global variable holds status information (warning exceptions) resulting from the execution of a fetch statement. Its value reflects the last cursor fetched. The value of @@sqlstatus is:
Value |
Description |
|---|---|
0 |
Indicates successful completion of the fetch statement. |
1 |
Indicates that the fetch statement resulted in an error. |
2 |
Indicates that there is no more data in the result set. This warning can occur if the current cursor position is on the last row in the result set and the client submits a fetch statement for that cursor. |
The @@fetch_status global variable provides information about whether fetch is executed successfully in a scrollable cursor. The value of @@fetch_status is:
Value |
Description |
|---|---|
0 |
Indicates successful completion of the fetch statement. |
-1 |
Indicates that the fetch operation failed, or the row fetched was beyond the result set. |
-2 |
Reserved for future use. |
Only a fetch statement can set @@sqlstatus and @@fetch_status. Other statements have no effect on @@sqlstatus or @@fetch_status.
The value of @@rowcount is affected by whether the specified cursor is forward-only or scrollable. If the cursor is the default, non-scrollable 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.
fetch_direction:
If not specified, the default value is next.
If not next, the cursor must be declared scrollable.
fetch_offset must be an exact, signed numeric, with a scale of zero.
Positions the cursor beyond the last row or before the first row, no data is returned and no error is raised.
Is absolute, when fetch_offset >0, the offset is calculated from the position before the first row of the result set. If fetch_offset <0, the offset is calculated from the position after the last row of the result set.
Is relative, when fetch_offset n>0, the cursor is placed n rows after the current position; if fetch_offset n<0, the cursor is placed abs (n) rows before the current position.
The row number specified in the result set is counted from 1; the first row is number 1.
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.
These terms are used in “Rules for positioning the scrollable cursor”.
curRowsetStart – the cursor’s current position.
new_CurRowsetStart – the new current position of the cursor.
total_rows – the total number of rows in the cursor result set.
before_first – the row position before the first row of the cursor result set. This variable has a value of 0.
after_last – the row position after the last row of the cursor result set. This variable has a value of total_rows + 1.
first_row – the position at the first row of the cursor result set. This variable has value of 1.
last_row – the position at the last row of the cursor result set. This variable has the same value as total_rows.
fetchSize – the number of rows requested for each fetch operation.
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:
The new_CurRowsetStart always moves to first_row, regardless of the position of CurRowsetStart and the value of fetchSize.
If total_rows >= fetchSize, then new_CurRowsetStart = total_rows – fetchSize + 1.
If total_rows < fetchSize, then new_CurRowsetStart is on first_row.
If CurRowsetStart is before_first, then new_CurRowsetStart is on first_row
Let curPos = (CurRowsetStart + fetchSize),
curPos <=total_rows, then new_CurRowsetStart = curPos
curPos > total_rows, new_CurRowsetStart is after_last
If CurRowsetStart is after_last row, then new_CurRowsetStart remains on after_last
new_CurRowsetStart is before_first when one of these conditions is true:
(CurRowsetStart >= 1) && (CurRowsetStart - fetchSize <=0)
CurRowsetStart is before_first
Let curPos = CurRowsetStart – fetchSize; iff 1 <=curPos <=total_rows, then new_CurRowsetStart = curPos.
If (CurRowsetStart is after_last), let curPos = total_rows – fetchSize + 1 new_CurRowsetStart = curPos if curPos > 0 new_CurRowsetStart is before_first if curPos <= 0
If (CurRowsetStart is before_first) && (fetch_offset > 0), then new_CurRowsetStart = fetch_offset.
new_CurRowsetStart is before_first if one of these conditions is true:
(CurRowsetStart is before_first) and (fetch_offset < 0)
(CurRowsetStart is on first_row) and (fetch_offset < 0)
(CurRowsetStart is after_last) and ((CurRowsetStart + fetch_offset + 1) <= 0)
If (1 < CurRowsetStart <= total_rows), let curPos = CurRowsetStart + fetch_offset, then:
new_CurRowsetStart is on first_row iff (curPos < 1) and abs (fetch_offset) <= fetchSize
new_CurRowsetStart is before first_row iff (curPos < 1) && (abs (fetch_offset) fetchSize)
new_CurRowsetStart = curPos iff (0 < curPos <=total_rows)
new_CurRowsetStart is after_last iff curPos > total_rows
If (CurRowsetStart is after_last), let curPos = CurRowsetStart + fetch_offset +1, then:
new_CurRowsetStart = curPos iff 1 <= curPos <= total_rows
new_CurRowsetStart is before_first iff curPos <= 0
new_CurRowsetStart is after_last iff curPos > total_rows
If fetch_offset = 0, new_CurRowsetStart is before_first
If fetch_offset > total_rows, new_CurRowsetStart is after_last
If 0 < fetch_offset <= total_rows, new_CurRowsetStart = fetch_offset
If (fetch_offset < 0) && (abs (fetch_offset) > total_rows), let abs_offset = abs (fetch_offset) new_CurRowsetStart is before_first iff abs_offset > fetchSize new_CurRowsetStart is on first_row iff abs_offset <= fetchSize
If (fetch_offset < 0) && (abs (fetch_offset) <= total_rows) new_CurRowsetStart = total_rows + fetch_offset + 1
ANSI SQL – Compliance level: Entry-level compliant.
The fetch of multiple rows is a Transact-SQL extension.
fetch permission defaults to all users.
Commands declare cursor, open, set
