| title | sp_cursorprepexec (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 08/20/2019 | ||
| ms.prod | sql | ||
| ms.prod_service | database-engine | ||
| ms.reviewer | |||
| ms.technology | system-objects | ||
| ms.topic | language-reference | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | 8094fa90-35b5-4cf4-8012-0570cb2ba1e6 | ||
| author | stevestein | ||
| ms.author | sstein |
[!INCLUDEappliesto-ss-asdb-asdw-pdw-md.md]
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 procedure is invoked by specifying ID = 5 in a tabular data stream (TDS) packet.
Transact-SQL Syntax Conventions
sp_cursorprepexec prepared handle OUTPUT, cursor OUTPUT, params , statement , options
[ , scrollopt [ , ccopt [ , rowcount ] ] ]
[, '@parameter_name[,...n ]']
prepared handle
Is a [!INCLUDEssNoVersion] generated prepared handle identifier. prepared handle is required and returns int.
cursor
Is the [!INCLUDEssNoVersion] generated cursor identifier. cursor is a required parameter that must be supplied on all subsequent procedures that act upon this cursor, for example, 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.
Note
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.
Note
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 require the following int input value.
| 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, [!INCLUDEssNoVersion] 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, [!INCLUDEssNoVersion] 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. |
parameter_name Designate one or more parameter names as defined in the params argument. There must be a parameter supplied for every parameter included in params. This argument is not required when the Transact-SQL statement or batch in params has no parameters defined.
If params returns a NULL value, then the statement is not parameterized.
sp_cursoropen (Transact-SQL)
sp_cursorexecute (Transact-SQL)
sp_cursorprepare (Transact-SQL)
sp_cursorfetch (Transact-SQL)
System Stored Procedures (Transact-SQL)