sp_cursoropen (Transact-SQL)

Applies to: SQL Server

Opens a cursor. sp_cursoropen defines the SQL statement associated with the cursor and cursor options, and then populates the cursor. sp_cursoropen is equivalent to the combination of the Transact-SQL statements DECLARE_CURSOR and OPEN. This procedure is invoked by specifying ID = 2 in a tabular data stream (TDS) packet.

Transact-SQL syntax conventions

Syntax

sp_cursoropen cursor OUTPUT
    , stmt
    [ , scrollopt [ OUTPUT ]
    [ , ccopt [ OUTPUT ]
    [ , rowcount OUTPUT [ , boundparam ] [ , ...n ] ] ] ]
[ ; ]

Arguments

cursor

A SQL Server-generated cursor identifier. cursor is a handle value that must be supplied on all subsequent procedures involving the cursor, such as sp_cursorfetch. The cursor parameter is int and can't be NULL.

cursor allows multiple cursors to be active on a single database connection.

stmt

A required parameter that defines the cursor result set. Any valid query string (syntax and binding) of any string type (regardless of Unicode, size, etc.) can serve as a valid stmt value type.

scrollopt

Scroll option. The scrollopt parameter is int, with a default of NULL, and can be one of the following values.

Because of the possibility that the requested value isn't appropriate for the cursor defined by stmt, this parameter serves as both input and output. In such cases, SQL Server assigns an appropriate value.

ccopt

Concurrency control option. ccopt is an optional parameter that requires one of the following int input values.

As with scrollopt, SQL Server can override the requested ccopt values.

rowcount

The number of fetch buffer rows to use with AUTO_FETCH. The default is 20 rows. rowcount behaves differently when assigned as an input value versus a return value.

boundparam

Signifies the use of extra parameters. boundparam is an optional parameter that should be specified if the scrollopt PARAMETERIZED_STMT value is set to ON.

Return code values

If no error is raised, sp_cursoropen returns one of the following values.

When an error is raised, the return values might be inconsistent and the accuracy can't be guaranteed.

When the rowcount parameter is specified as a return value, the following result set occurs.

If RPC is in use, the return values are as follows.

If the sp_cursoropen procedure executes successfully, the RPC return parameters and a result set with TDS column format information (0xa0 and 0xa1 messages) are sent. If unsuccessful, one or more TDS error messages are sent. In either case, no row data is returned and the DONE message count is 0. 0x81 is returned (standard for SELECT statements) along with the 0xa5 and 0xa4 token streams.

Remarks

The stmt parameter

If stmt specifies the execution of a stored procedure, the input parameters might either be defined as constants as part of the stmt string, or specified as boundparam arguments. Declared variables can be passed as bound parameters in this way.

The allowed contents of the stmt parameter depend upon whether or not the ccopt ALLOW_DIRECT return value was linked by OR to the rest of the ccopt values:

• If ALLOW_DIRECT isn't specified, either a Transact-SQL SELECT or EXECUTE statement calling for a stored procedure containing a single SELECT statement must be used. Furthermore, the SELECT statement must qualify as a cursor; that is, it can't contain the keywords SELECT INTO or FOR BROWSE.

• If ALLOW_DIRECT is specified, this might result in one or more Transact-SQL statements, including statements that execute other stored procedures with multiple statements. Non-SELECT statements or any SELECT statement that contains the keywords SELECT INTO or FOR BROWSE are executed, and don't result in the creation of a cursor. The same is true for any SELECT statement included in a batch of multiple statements. In cases where a SELECT statement contains clauses that pertain only to cursors, those clauses are ignored. For instance, when the value of ccopt is 0x2002, this is a request for:

  • A cursor with scroll locks, if there's only a single SELECT statement that qualifies as a cursor, or

  • A direct statement execution if there are multiple statements, a single non-SELECT statement, or a SELECT statement that doesn't qualify as a cursor.

The scrollopt parameter

The first five scrollopt values (KEYSEY, DYNAMIC, FORWARD_ONLY, STATIC, and FAST_FORWARD) are mutually exclusive.

PARAMETERIZED_STMT and CHECK_ACCEPTED_TYPES can be linked by OR to any of the first five values.

AUTO_FETCH and AUTO_CLOSE can be linked by OR to FAST_FORWARD.

If CHECK_ACCEPTED_TYPES is ON, at least one of the last five scrollopt values (KEYSET_ACCEPTABLE, DYNAMIC_ACCEPTABLE, FORWARD_ONLY_ACCEPTABLE, STATIC_ACCEPTABLE, or FAST_FORWARD_ACCEPTABLE) must also be ON.

STATIC cursors are always opened as READ_ONLY. This means that the underlying table can't be updated through this cursor.

The ccopt parameter

The first four ccopt values (READ_ONLY, SCROLL_LOCKS, and both OPTIMISTIC values) are mutually exclusive.

ALLOW_DIRECT and CHECK_ACCEPTED_TYPES can be linked by OR to any of the first four values.

UPDT_IN_PLACE can be linked by OR to READ_ONLY, SCROLL_LOCKS, or either of the OPTIMISTIC values.

If CHECK_ACCEPTED_TYPES is ON, at least one of the last four ccopt values (READ_ONLY_ACCEPTABLE, SCROLL_LOCKS_ACCEPTABLE, and either of the OPTIMISTIC_ACCEPTABLE values) must also be ON.

Positioned UPDATE and DELETE functions might be performed only within the fetch buffer and only if the ccopt value equals SCROLL_LOCKS or OPTIMISTIC. If SCROLL_LOCKS is the specified value, the operation is guaranteed to succeed. If OPTIMISTIC is the specified value, the operation fails if the row changed since it was last fetched.

The reason for this failure is that when OPTIMISTIC is the specified value, an optimistic currency control function is performed by comparing timestamps or checksum values, as determined by SQL Server. If any of these rows don't match, the operation fails.

Specifying UPDT_IN_PLACE as the return value governs the following results:

• If not set when performing a positioned update on a table with a unique index, the cursor deletes the row from its work table and inserts it at the end of any of the key columns used by the cursor, which changes those columns.

• If set ON, the cursor updates the key columns in the work table's original row.

The bound_param parameter

The parameter name should be paramdef when PARAMETERIZED_STMT is specified, according to the error message in the code. When PARAMETERIZED_STMT isn't specified, no name is specified in the error message.

RPC considerations

The RPC RETURN_METADATA input flag can be set to 0x0001 to request that cursor select list metadata is returned in the TDS stream.

Examples

A. The bound_param parameter

Any parameters after the fifth are passed along to the statement plan as input parameters. The first such parameter must be a string in the following form:

<parameter_name> <data_type> [ ,... n ]

Subsequent parameters are used to pass the values to be substituted for the <parameter_name> in the statement.

Related content

• sp_cursorfetch (Transact-SQL)
• System stored procedures (Transact-SQL)