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.
|Applies to: SQL Server ( SQL Server 2008 through current version).|
sp_cursorfetch cursor [ , fetchtype[ , rownum [ , nrows] ]]
Is 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 later in this topic.
Specifies which cursor buffer to fetch. fetchtype is an optional parameter that requires one of the following integer input values.
|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.|
There is no support for the value 0x40.
For more information, see the Remarks section later in this topic.
Is 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 later in this topic.
Is an optional parameter that is used to specify the number of rows to fetch. If nrows is not 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 later in this topic.
Return Code Values
When you specify the bit value INFO, the values that may be returned are shown in the following tables.
: If no rows are returned, the buffer contents remain as they were.
|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 to be fetched in this case, the last two rows of the result set are returned.
|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|
Before there have been any fetch operations, the default position of a cursor is before the first row of the result set.
Except for SKIP_UPD_CNCY, the fetchtype values are mutually exclusive.
When SKIP_UPDT_CNCY is specified, the timestamp column values are not 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 has 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 column(s) 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.
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 fetchtype values REFRESH and INFO ignore this parameter.
When you specify a fetchtype value of FIRST that has an nrow value of 0, the cursor is positioned before the result set that has no rows in the fetch buffer.
When you specify a fetchtype value of LAST that has an nrow value of 0, the cursor is positioned after the result set that has no rows in the current fetch buffer.
For the fetchtype values of NEXT, PREV, ABSOLUTE, RELATIVE, and PREV_NOADJUST, an nrow value of 0 is not valid.
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.
|0||Procedure executed successfully.|
|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, that is: column format (0x2a), rows (0xd1), followed by done (0xfd). Metadata tokens are sent in the same format as specified for sp_cursoropen, that is: 0x81, 0xa5 and 0xa4 for SQL Server 7.0 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 INT4. This rowstat column has one of the values shown in the following table.
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 set to null, fixed length fields set to 0, blank, or the default for that column, as appropriate).
The DONE rowcount will always be zero. 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 be returned in the TDS stream, set the RPC RETURN_METADATA input flag to 1.
A. Using 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 that has an nrows value of 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 will return rows that were in the PRIOR fetch buffer.
This is the exact case in which the RPC status parameter is set to 2.
B. Using 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 earlier, 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