Was this page helpful?
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

sp_cursorexecute (Transact-SQL)


Applies To: SQL Server 2014, SQL Server 2016 Preview

Creates and populates a cursor based upon the execution plan created by sp_cursorprepare. This procedure, coupled with sp_cursorprepare, has the same function as sp_cursoropen, but is split into two phases. sp_cursorexecute is invoked by specifying ID =4 in a tabular data stream (TDS) packet.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

sp_cursorexecute prepared_handle, cursor     [ , scrollopt [ OUTPUT ]
    [ , ccopt [ OUTPUT ]
    [ ,rowcount OUTPUT [ ,bound param] [,...n] ] ] ] ] 


Is the prepared statement handle value returned by sp_cursorprepare. prepared_handle is a required parameter that calls for an int input value.


Is the SQL Server-generated cursor identifier. cursor is a required parameter that must be supplied on all subsequent procedures which act upon the cursor, such as sp_cursorfetch


Scroll option. scrollopt is an optional parameter that requires an int input value. The sp_cursorexecute scrollopt parameter has the same value options as those for sp_cursoropen.


The PARAMETERIZED_STMT value is not supported.


If a scrollopt value is not specified, the default value is KEYSET regardless of scrollopt value specified in sp_cursorprepare.


Currency control option. ccopt is an optional parameter that requires an int input value. The sp_cursorexecute ccopt parameter has the same value options as those for sp_cursoropen.


If a ccopt value is not specified, the default value is OPTIMISTIC regardless of ccopt value specified in sp_cursorprepare.


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.


Signifies the optional use of additional parameters.


Any parameters after the fifth are passed along to the statement plan as input parameters.

rowcount may return the following values.




Number of rows unknown.


An asynchronous population is in effect.

scrollopt and ccopt are useful when the cached plans are preempted for the server cache, meaning that the prepared handle identifying the statement must be recompiled. The scrollopt and ccopt parameter values must match the values sent in the original request to sp_cursorprepare.


PARAMETERIZED_STMT should not be assigned to scrollopt.

Failure to provide matching values will result in recompilation of the plans, negating the prepare and execute operations.

The RPC RETURN_METADATA input flag can be set to 1 to request that cursor select list metadata be returned in the TDS stream.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft