sp_cursorfetch (Transact-SQL)
Applies to: 
SQL Server
Fetches a buffer of one or more rows from the database. The group of rows in this buffer is called the cursor's fetch buffer. sp_cursorfetch is invoked by specifying ID = 7 in a tabular data stream (TDS) packet.
Transact-SQL syntax conventions
Syntax
sp_cursorfetch cursor
[ , fetchtype [ , rownum [ , nrows ] ] ]
[ ; ]
Arguments
cursor
A handle value generated by SQL Server and returned by sp_cursoropen. cursor is a required parameter that calls for an int input value. For more information, see the Remarks section.
fetchtype
Specifies which cursor buffer to fetch. fetchtype is an optional parameter that requires one of the following integer input values.
| Value | Name | Description |
|---|---|---|
0x0001 |
FIRST |
Fetches the first buffer of nrows rows. If nrows equals 0, the cursor is positioned before the result set and no rows are returned. |
0x0002 |
NEXT |
Fetches the next buffer of nrows rows. |
0x0004 |
PREV |
Fetches the previous buffer of nrows rows. Note: Using PREV for a FORWARD_ONLY cursor returns an error message because FORWARD_ONLY only supports scrolling in one direction. |
0x0008 |
LAST |
Fetches the last buffer of nrows rows. If nrows equals 0, the cursor is positioned after the result set and no rows are returned. Note: Using LAST for a FORWARD_ONLY cursor returns an error message because FORWARD_ONLY only supports scrolling in one direction. |
0x10 |
ABSOLUTE |
Fetches a buffer of nrows rows starting with the rownum row. Note: Using ABSOLUTE for either a DYNAMIC cursor or a FORWARD_ONLY cursor returns an error message because FORWARD_ONLY only supports scrolling in one direction. |
0x20 |
RELATIVE |
Fetches the buffer of nrows rows starting with the row that is specified as being the rownum value of rows from the first row in the current block. In this case, rownum can be a negative number. Note: Using RELATIVE for a FORWARD_ONLY cursor returns an error message because FORWARD_ONLY only supports scrolling in one direction. |
0x80 |
REFRESH |
Refills the buffer from underlying tables. |
0x100 |
INFO |
Retrieves information about the cursor. This information is returned by using the rownum and nrows parameters. Therefore, when INFO is specified, rownum and nrows become output parameters. |
0x200 |
PREV_NOADJUST |
Is used like PREV. However, if the top of the result set is encountered prematurely, the results might vary. |
0x400 |
SKIP_UPDT_CNCY |
Must be used with one of the other fetchtype values, except for INFO. |
Note
There's no support for the value 0x40.
For more information, see the Remarks section.
rownum
An optional parameter that is used to specify the row position for the ABSOLUTE and INFO fetchtype values by using only integer values for input or output, or both. rownum serves as the row offset for the fetchtype bit value RELATIVE. rownum is ignored for all other values. For more information, see the Remarks section.
nrows
An optional parameter that is used to specify the number of rows to fetch. If nrows isn't specified, the default value is 20 rows. To set the position without returning data, specify a value of 0. When nrows is applied to the fetchtype INFO query, it returns the total number of rows in that query.
nrows is ignored by the REFRESH fetchtype bit value. For more information, see the Remarks section.
Return code values
When you specify the bit value INFO, the values that might be returned are shown in the following tables.
If no rows are returned, the buffer contents remain as they were.
| <rownum> | Set to |
|---|---|
| If not open | 0 |
| If positioned before the result set | 0 |
| If positioned after the result set | -1 |
For KEYSET and STATIC cursors |
The absolute row number of the current position in the result set |
For DYNAMIC cursors |
1 |
For ABSOLUTE |
-1 returns the last row in a set. -2 returns the second to last row in a set, and so on. Note: If more than one row is requested for fetching in this case, the last two rows of the result set are returned. |
<nrows> |
Set to |
|---|---|
| If not open | 0 |
For KEYSET and STATIC cursors |
Typically, the current keyset size.-m if the cursor is in asynchronous creation with m rows found to this point. |
For DYNAMIC cursors |
-1 |
Remarks
The cursor parameter
Before any fetch operations, the default position of a cursor is before the first row of the result set.
The fetchtype parameter
Except for SKIP_UPD_CNCY, the fetchtype values are mutually exclusive.
When SKIP_UPDT_CNCY is specified, the timestamp column values aren't written to the keyset table when a row is fetched or refreshed. If the keyset row is being updated, the values of the timestamp columns remain as the previous value. If the keyset row is being inserted, the values for the timestamp columns are undefined.
For KEYSET cursors, this means that the keyset table has the values set during the last nonskip FETCH, if one was performed. If not, it's the values set during population.
For DYNAMIC cursors, this means that if the skip is performed with a refresh, it produces the same results as KEYSET. For any other fetch type, the keyset table is truncated. This means that the rows are being inserted and the values for the timestamp columns are undefined. Therefore, when you run sp_cursorfetch for DYNAMIC cursors, avoid using SKIP_UPDT_CNCY for any operation other than REFRESH.
If a fetch operation fails because the requested cursor position is beyond the result set, the cursor position is set just after the last row. If a fetch operation fails because the requested cursor position is positioned before the result set, the cursor position is set before the first row.
The rownum parameter
When you use rownum, the buffer is filled starting with the specified row.
The fetchtype value ABSOLUTE refers to the position of rownum within the whole result set. A negative number with ABSOLUTE specifies that the operation counts rows from the end of the result set.
The fetchtype value RELATIVE refers to the position of rownum in relation to the position of the cursor at the start of the current buffer. A negative number with RELATIVE specifies that the cursor go backward from the current cursor position.
The nrows parameter
The fetchtype values REFRESH and INFO ignore this parameter.
When you specify a fetchtype value of FIRST that's an nrow value of 0, the cursor is positioned before the result set that's no rows in the fetch buffer.
When you specify a fetchtype value of LAST that's an nrow value of 0, the cursor is positioned after the result set that's no rows in the current fetch buffer.
For the fetchtype values of NEXT, PREV, ABSOLUTE, RELATIVE, and PREV_NOADJUST, an nrow value of 0 isn't valid.
RPC considerations
The RPC return status indicates whether the keyset size parameter is final or not; that is, if the keyset or temporary table is being populated asynchronously.
The RPC status parameter is set to one of the values shown in the following table.
| Value | Description |
|---|---|
0 |
Procedure executed successfully. |
0x0001 |
Procedure failed. |
0x0002 |
A fetch in a negative direction caused the cursor position to be set to the beginning of the result set, when the fetch would have logically been before the results. |
0x10 |
A fast-forward cursor was automatically closed. |
The rows are returned as a typical result set: column format (0x2a), rows (0xd1), followed by DONE (0xfd). Metadata tokens are sent in the same format as specified for sp_cursoropen: 0x81, 0xa5 and 0xa4 for SQL Server users, and so on. The row status indicators are sent as hidden columns, similar to BROWSE mode, at the end of each row with the column name rowstat and data type int. This rowstat column has one of the values shown in the following table.
| Value | Description |
|---|---|
0x0001 |
FETCH_SUCCEEDED |
0x0002 |
FETCH_MISSING |
Because the TDS protocol provides no way to send the trailing status column without sending the previous columns, dummy data is sent for missing rows. Nullable fields are set to null, and fixed length fields are set to 0, blank, or the default for that column, as appropriate.
The DONE rowcount is always 0. The DONE message contains the actual result set rowcount, and error or informational messages might appear between any TDS messages.
To request that metadata about the cursor's select list is returned in the TDS stream, set the RPC RETURN_METADATA input flag to 1.
Examples
A. Use PREV to change a cursor position
Assume that a cursor h2 would produce a result set having the following contents with a current position as shown:
row 1 contents
row 2 contents
row 3 contents
row 4 contents <-- current position
row 5 contents
row 6 contents
Next, an sp_cursorfetch PREV where nrows is 5 would logically position the cursor two rows before the first row of the result set. In these cases, the cursor is adjusted to start at the first row and return the number of rows requested. This frequently means that it returns rows that were in the PRIOR fetch buffer.
Note
This is the exact case in which the RPC status parameter is set to 2.
B. Use PREV_NOADJUST to return fewer rows than PREV
PREV_NOADJUST never includes any of the rows at or after the current cursor position in the block of rows that it returns. In cases where PREV returns rows after the current position, PREV_NOADJUST returns fewer rows than requested in nrows. Given the current position in Example A previously, when PREV is applied, sp_cursorfetch (h2, 4, 1, 5) fetches the following rows:
row1 contents
row2 contents
row3 contents
row4 contents
row5 contents
However, when PREV_NOADJUST is applied, sp_cursorfetch (h2, 512, 6, 5) fetches only the following rows:
row1 contents
row2 contents
row3 contents