--- title: "sp_cursor (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "03/03/2017" ms.prod: "sql-non-specified" ms.reviewer: "" ms.suite: "" ms.technology: - "database-engine" ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords: - "sp_cursor_TSQL" - "sp_cursor" dev_langs: - "TSQL" helpviewer_keywords: - "sp_cursor" ms.assetid: 41ade0ca-5f11-469d-bd4d-c8302ccd93b3 caps.latest.revision: 10 author: "BYHAM" ms.author: "rickbyh" manager: "jhubbard" --- # sp_cursor (Transact-SQL) [!INCLUDE[tsql-appliesto-ss2008-xxxx-xxxx-xxx_md](../../includes/tsql-appliesto-ss2008-xxxx-xxxx-xxx-md.md)] 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. || |-| |**Applies to**: SQL Server ([!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] through [current version](http://go.microsoft.com/fwlink/p/?LinkId=299658)).| ![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif "Topic link icon") [Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md) ## Syntax ``` sp_cursor cursor, optype, rownum, table [ , value[...n]]] ``` ## Arguments *cursor* The cursor handle. *cursor* is a required parameter that calls for an **int** input value. *cursor* is the *handle* value generated by SQL Server and returned by the sp_cursoropen procedure. *optype* Is a required parameter that designates what operation the cursor will perform. *optype* requires one of the following **int** input values. |Value|Name|Description| |-----------|----------|-----------------| |0X0001|UPDATE|Is used to update one or more rows in the fetch buffer. The rows specified in *rownum* are re-accessed and updated.| |0x0002|DELETE|Is used to delete one or more rows in the fetch buffer. The rows specified in *rownum* are re-accessed and deleted.| |0X0004|INSERT|Inserts data without building an SQL **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 a SQL Server U-Lock to be acquired on the page containing the specified row. This lock is compatible with S-Locks but not with X-Locks or other U-Locks. Can be used to implement short-term locking.| |0X20|SETPOSITION|Is used only when the program is going to issue a subsequent SQL Server positioned DELETE or UPDATE statement.| |0X40|ABSOLUTE|Can only be used in conjunction with UPDATE or DELETE. ABSOLUTE is used only with KEYSET cursors (is ignored for DYNAMIC cursors and STATIC cursors cannot be updated0.

Note: If ABSOLUTE is specified on a row in the keyset that has not been fetched, the operation may fail the concurrency check and the return result cannot be guaranteed.| *rownum* Specifies which of the rows in the fetch buffer the cursor will operate on, update, or delete. > [!NOTE] > This does not 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. 1 Signifies the first row in the fetch buffer. 2 Signifies second row in the fetch buffer. 3, 4, 5 Signifies the third row, and so forth. n Signifies the nth row in the fetch buffer. 0 Signifies all rows in the fetch buffer. > [!NOTE] > 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. *table* is an optional parameter that requires a String input value. The string can be specified as any character or UNICODE data type. *table* can be 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. > [!NOTE] > The parameter names for *value* can be assigned by the user. ## Return Code Values When using an RPC, a positioned DELETE or UPDATE operation with a buffer number 0 will return a DONE message with a *rowcount* of 0 (failure) or 1 (success) for every row in the fetch buffer. ## Remarks ## optype Parameter With the exception of 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 are not included, the INSERT fails. - The SETPOSITION value does not affect the starting point of any RELATIVE, NEXT, or PREVIOUS fetch operation, nor do any updates or deletes performed using the sp_cursor interface. Any number that does not specify a row in the fetch buffer will result in the position being set to 1 with no error being returned. Once SETPOSITION is executed, the position remains in effect until the next sp_cursorfetch operation, T-SQL **FETCH** operation, or sp_cursor SETPOSITION operation through the same cursor. A subsequent sp_cursorfetch operation will set the position of the cursor to the first row in the new fetch buffer while other cursor calls will not affect the value of the position. SETPOSITION can be linked by an OR clause with REFRESH, UPDATE, DELETE, or LOCK in order to set the value of the position to the last modified row. If a row in the fetch buffer is not specified through the *rownum* parameter, the position will be 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 be linked by an OR clause with REFRESH, UPDATE, DELETE, or LOCK to set the cursor position to the last modified row. ## 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 (this can be done through a transaction). For OPTIMISTIC cursors, you must have previously fetched the row to perform this operation. ## 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 may 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 will substitute the view name). ## value Parameter There are two alternatives to the rules for using *value* as stated earlier in the Arguments section: 1. You can use a name that is '@' pre-pended to the name of the column in the select-list for any named *value* parameters. One advantage of this alternative is that data conversion may not be necessary. 2. Use a parameter to either submit a complete UPDATE or INSERT statement or use multiple parameters to submit portions of an UPDATE or INSERT statement which SQL Server will then build into a complete statement. Examples of this can be found in the Examples section later in this topic. ## Examples ### Alternative value Parameter Uses For UPDATE: When a single parameter is used, an UPDATE statement may be submitted using the following syntax: `[ [ UPDATE ] SET ] { = expression} [,…n]` > [!NOTE] > If UPDATE \
is specified, any value specified for the *table* parameter will be ignored. When multiple parameters are used, the first parameter must be a string in the following form: `[ SET ] = expression [,...n]` and the subsequent parameters must be in the form of: ` = expression [,...n]` In this case, the \
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 may be submitted using the following syntax: `[ [ INSERT [INTO]
] VALUES ] ( [,...n] )` > [!NOTE] > If INSERT *\
* is specified, any value specified for the *table* parameter will be ignored. When multiple parameters are used, the first parameter must be a string in the following form: `[ VALUES ( ] [,...n]` and 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 *\
* in the constructed UDPATE statement is the one either specified or defaulted to by the *table* parameter. > [!NOTE] > It is possible to submit one parameter as a named parameter, i.e. "`@VALUES`". In this case no other named parameters may be used. ## See Also [sp_cursoropen (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-cursoropen-transact-sql.md) [sp_cursorfetch (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-cursorfetch-transact-sql.md) [System Stored Procedures (Transact-SQL)](../../relational-databases/system-stored-procedures/system-stored-procedures-transact-sql.md)