Share via

sp_cursorfetch (Transact-SQL)

  • Article

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.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_cursorfetch cursor 
    [ , fetchtype [ , rownum [ , nrows ] ] ]

Arguments

  • cursor
    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.

  • 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 is no support for the value 0x40.

    For more information, see the Remarks section later in this topic.

  • rownum
    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.

  • nrows
    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.

    Note

       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.

Note

:   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 to be fetched 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

cursor Parameter

Before there have been any fetch operations, the default position of a cursor is before the first row of the result set.

fetchtype Parameter

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.

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.

nrows Parameter

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.

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

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

Examples

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.

Note

 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

See Also

Reference

sp_cursoropen (Transact-SQL)

System Stored Procedures (Transact-SQL)