sp_cursorprepare (Transact-SQL)

Compiles the cursor statement or batch into an execution plan, but does not create the cursor. The compiled statement can later be used by sp_cursorexecute. This procedure, coupled with sp_cursorexecute, has the same function as sp_cursoropen, but is split into two phases. sp_cursorprepare is invoked by specifying ID = 3 in a tabular data stream (TDS) packet.

Transact-SQL Syntax Conventions

sp_cursorprepare prepared_handle OUTPUT, params , stmt , options    [ , scrollopt[ , ccopt]]

Arguments

• prepared_handle
  A SQL Server-generated prepared handle identifier that returns an integer value.

  Note

  prepared_handle is subsequently supplied to a sp_cursorexecute procedure in order to open a cursor. Once a handle is created, it exists until you log off or until you explicitly remove it through a sp_cursorunprepare procedure.

• 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. Input a NULL value if the statement is not parameterized.

  Note

  Use an ntext string as the input value when stmt is parameterized and the scrollopt PARAMETERIZED_STMT value is ON.

• stmt
  Defines the cursor result set. The stmt 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 requires 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 the requested value might not be 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.

  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 from the one requested.

Remarks

The RPC status parameter is one of the following:

Examples

When stmt is parameterized and the scrollopt PARAMETERIZED_STMT value is ON, the format of the string is as follows:

{ <local variable name><data type> } [ ,…n ]

See Also

Other Resources

sp_cursoropen (Transact-SQL)

sp_cursorexecute (Transact-SQL)

sp_cursorunprepare (Transact-SQL)

System Stored Procedures (Transact-SQL)