Udostępnij za pośrednictwem


sp_cursorprepexec (Transact-SQL)

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

  • 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.

Return Code Values

If params returns a NULL value then the statement is not parameterized.

Zobacz także

Odwołanie

sp_cursoropen (Transact-SQL)

sp_cursorexecute (Transact-SQL)

sp_cursorprepare (Transact-SQL)

sp_cursorfetch (Transact-SQL)

System Stored Procedures (Transact-SQL)