sp_cursorprepexec (Transact-SQL)

Artykuł
12/08/2014

Compiles a plan for the submitted cursor statement or batch, then creates and populates the cursor. sp_cursorprepexec combines the functions of sp_cursorprepare and sp_cursorexecute. This procedures is invoked by specifying ID = 5 in a tabular data stream (TDS) packet.

Ikona łącza do tematu Transact-SQL Syntax Conventions

Składnia

sp_cursorprepexec prepared handle OUTPUT, cursor OUTPUT, params , statement , options
    [ , scrollopt [ , ccopt [ , rowcount ] ] ]

Arguments

prepared handle
Is a SQL Server generated prepared handle identifier. prepared handle is required and returns int.
cursor
Is the SQL Server generated cursor identifier. cursor is a required parameter that must be supplied on all subsequent procedures which act upon this cursor, e.g. sp_cursorfetch.
params
Identifies parameterized statements. The params definition of variables is substituted for parameter markers in the statement. params is a required parameter that calls for an ntext, nchar, or nvarchar input value.

[!UWAGA]

Use an ntext string as the input value when stmt is parameterized and the scrollopt PARAMETERIZED_STMT value is ON.
statement
Defines the cursor result set. The statement parameter is required and calls for an ntext, nchar or nvarchar input value.

[!UWAGA]

The rules for specifying the stmt value are the same as those for sp_cursoropen, with the exception that the stmt string data type must be ntext.
options
An optional parameter that returns a description of the cursor result set columns. options requires the following int input value.

Value

Description

0x0001

RETURN_METADATA

Value	Description
0x0001	RETURN_METADATA

scrollopt
Scroll Option. scrollopt is an optional parameter that requires one of the following int input values.

Value	Description
0x0001	KEYSET
0x0002	DYNAMIC
0x0004	FORWARD_ONLY
0x0008	STATIC
0x10	FAST_FORWARD
0x1000	PARAMETERIZED_STMT
0x2000	AUTO_FETCH
0x4000	AUTO_CLOSE
0x8000	CHECK_ACCEPTED_TYPES
0x10000	KEYSET_ACCEPTABLE
0x20000	DYNAMIC_ACCEPTABLE
0x40000	FORWARD_ONLY_ACCEPTABLE
0x80000	STATIC_ACCEPTABLE
0x100000	FAST_FORWARD_ACCEPTABLE

Because of the possibility that the requested option is not appropriate for the cursor defined by <stmt>, this parameter serves as both input and output. In such cases, SQL Server assigns an appropriate type and modifies this value.

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

Value	Description
0x0001	READ_ONLY
0x0002	SCROLL_LOCKS (previously known as LOCKCC)
0x0004	OPTIMISTIC (previously known as OPTCC)
0x0008	OPTIMISTIC (previously known as OPTCCVAL)
0x2000	ALLOW_DIRECT
0x4000	UPDT_IN_PLACE
0x8000	CHECK_ACCEPTED_OPTS
0x10000	READ_ONLY_ACCEPTABLE
0x20000	SCROLL_LOCKS_ACCEPTABLE
0x40000	OPTIMISTIC_ACCEPTABLE
0x80000	OPTIMISITC_ACCEPTABLE

As with scrollpt, SQL Server can assign a different value than the one requested.

rowcount
Is an optional parameter that signifies 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.

As input value	As return value
When AUTO_FETCH is specified with FAST_FORWARD cursors rowcount represents the number of rows to place into the fetch buffer.	Represents the number of rows in the result set. When the scrollopt AUTO_FETCH value is specified, rowcount returns the number of rows that were fetched into the fetch buffer.