Share via

  • Article

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.

Topic link iconTransact-SQL Syntax Conventions

Syntax

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:

Value

Description

0

Success

0x0001

Failure

1FF6

Could not return metadata.

NoteNote
The reason for this is that the statement does not produce a result set; for example, it is an INSERT or DDL statement.

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 ]