sp_cursor (Transact-SQL)
Applies to: 
SQL Server
Requests positioned updates. This procedure performs operations on one or more rows within a cursor's fetch buffer. sp_cursor is invoked by specifying ID = 1 in a tabular data stream (TDS) packet.
Transact-SQL syntax conventions
Syntax
sp_cursor cursor , optype , rownum , table
[ , value [ ...n ] ]
[ ; ]
Arguments
cursor
The cursor handle. The cursor parameter is int, and can't be NULL. This parameter is the handle value generated by the Database Engine and returned by the sp_cursoropen procedure.
optype
A required parameter that designates what operation the cursor performs. The optype parameter requires one of the following values.
| Value | Name | Description |
|---|---|---|
0X0001 |
UPDATE |
Is used to update one or more rows in the fetch buffer. The rows specified in rownum are reaccessed and updated. |
0x0002 |
DELETE |
Is used to delete one or more rows in the fetch buffer. The rows specified in rownum are reaccessed and deleted. |
0X0004 |
INSERT |
Inserts data without building an INSERT statement. |
0X0008 |
REFRESH |
Is used to refill the buffer from underlying tables and can be used to refresh the row if an update or delete fails due to optimistic concurrency control, or after an UPDATE. |
0X10 |
LOCK |
Causes an update lock (U) to be acquired on the page containing the specified row. This lock is compatible with shared locks (S) but not with exclusive locks (X) or other update locks. Can be used to implement short-term locking. |
0X20 |
SETPOSITION |
Is used only when the program is going to issue a subsequent DELETE or UPDATE statement. |
0X40 |
ABSOLUTE |
Can only be used with UPDATE or DELETE. ABSOLUTE is used only with KEYSET cursors (is ignored for DYNAMIC cursors) and STATIC cursors can't be updated.Note: If ABSOLUTE is specified on a row in the keyset that hasn't been fetched, the operation might fail the concurrency check and the return result can't be guaranteed. |
rownum
Specifies which of the rows in the fetch buffer the cursor operates on, updates, or deletes. This parameter doesn't affect the starting point of any RELATIVE, NEXT, or PREVIOUS fetch operation, nor any updates or deletes performed using sp_cursor.
rownum is a required parameter that calls for an int input value.
1Signifies the first row in the fetch buffer.
2, 3, 4, ...nSignifies the second, third, and fourth row, and so on.
0Signifies all rows in the fetch buffer.
This parameter is only valid for use with UPDATE, DELETE, REFRESH, or LOCK optype values.
table
Table name that identifies the table that optype applies to when the cursor definition involves a join, or ambiguous column names are returned by the value parameter. If no specific table is designated, the default is the first table in the FROM clause. The table parameter is optional, and requires a string input value. The string can be specified as any character or Unicode data type, or a multi-part table name.
value
Used to insert or update values. The value string parameter is only used with UPDATE and INSERT optype values. The string can be specified as any character or Unicode data type.
The parameter names for value can be assigned by the user.
Return code values
0 (success) or 1 (failure).
Remarks
The optype parameter
Except for the combinations of SETPOSITION with UPDATE, DELETE, REFRESH, or LOCK; or ABSOLUTE with either UPDATE or DELETE, the optype values are mutually exclusive.
The SET clause of the UPDATE value is constructed from the value parameter.
One benefit of using the INSERT <optype> value is that you can avoid converting non-character data into character format for inserts. The values are specified in the same way as UPDATE. If any required columns aren't included, the INSERT fails.
- The
SETPOSITIONvalue doesn't affect the starting point of anyRELATIVE,NEXT, orPREVIOUSfetch operation, nor do any updates or deletes performed using thesp_cursorinterface. Any number that doesn't specify a row in the fetch buffer, results in the position being set to1, with no error being returned. OnceSETPOSITIONis executed, the position remains in effect until the nextsp_cursorfetchoperation, T-SQLFETCHoperation, orsp_cursorSETPOSITIONoperation through the same cursor. A subsequentsp_cursorfetchoperation sets the position of the cursor to the first row in the new fetch buffer while other cursor calls don't affect the value of the position.SETPOSITIONcan be linked by anORclause withREFRESH,UPDATE,DELETE, orLOCKin order to set the value of the position to the last modified row.
If a row in the fetch buffer isn't specified through the rownum parameter, the position is set to 1, with no error returned. Once the position is set, it remains in effect until the next sp_cursorfetch operation, T-SQL FETCH operation, or sp_cursor SETPOSITION operation is performed on the same cursor.
SETPOSITION can be linked by an OR clause with REFRESH, UPDATE, DELETE, or LOCK to set the cursor position to the last modified row.
The rownum parameter
If specified, the rownum parameter can be interpreted as the row number within the keyset instead of the row number within the fetch buffer. The user is responsible for ensuring that concurrency control is maintained. This means that for SCROLL_LOCKS cursors, you must independently maintain a lock on the given row (which can be done through a transaction). For OPTIMISTIC cursors, you must have previously fetched the row to perform this operation.
The table parameter
If the optype value is UPDATE or INSERT and a full update or insert statement is submitted as the value parameter, the value specified for table is ignored.
Note
Pertaining to views, only one table participating in the view might be modified. The value parameter column names must reflect the column names in the view, but the table name can be that of the underlying base table (in which case sp_cursor substitutes the view name).
The value parameter
There are two alternatives to the rules for using value as stated earlier in the Arguments section:
You can use a name that is
@prepended to the name of the column in the select-list for any named value parameters. One advantage of this alternative is that data conversion might not be necessary.Use a parameter to either submit a complete
UPDATEorINSERTstatement or use multiple parameters to submit portions of anUPDATEorINSERTstatement, which the Database Engine then builds into a complete statement. Examples can be found in the Examples section later in this article.
Examples
Alternative value parameter uses
For UPDATE
When a single parameter is used, an UPDATE statement might be submitted using the following syntax:
[ [ UPDATE <table_name> ] SET ] { <column name> = expression } [ , ...n ]
If UPDATE <table_name> is specified, any value specified for the table parameter is ignored.
When multiple parameters are used, the first parameter must be a string in the following form:
[ SET ] <column name> = expression [ , ...n ]
The subsequent parameters must be in the form of:
<column name> = expression [ , ...n ]
In this case, the <table_name> in the constructed update statement is the one either specified or defaulted to by the table parameter.
For INSERT
When a single parameter is used, an INSERT statement might be submitted using the following syntax:
[ [ INSERT [ INTO ] <table_name> ] VALUES ] ( <expression> [ , ...n ] )
If INSERT <table_name> is specified, any value specified for the table parameter is ignored.
When multiple parameters are used, the first parameter must be a string in the following form:
[ VALUES ] ( <expression> [ , ...n ] )
The subsequent parameters must be in the form of expression [ , ...n ], except where VALUES was specified, in which case there must be a trailing ) after the last expression. In this case, the <table_name> in the constructed UPDATE statement is the one either specified or defaulted to by the table parameter.
Note
It's possible to submit one parameter as a named parameter, for example @values. In this case no other named parameters can be used.