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
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
If positioned before the result set
If positioned after the result set
For KEYSET and STATIC cursors
The absolute row number of the current position in the result set
For DYNAMIC cursors
-1 returns the last row in a set.
-2 returns the second to last row in a set, and so on.
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
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
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.
Procedure executed successfully.
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.
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