Edit

Share via

sp_prepexec (Transact-SQL)

  • Article

Applies to: SQL Server

Prepares and executes a parameterized Transact-SQL statement. sp_prepexec combines the functions of sp_prepare and sp_execute. This action is invoked by ID = 13 in a tabular data stream (TDS) packet.

Transact-SQL syntax conventions

Syntax

sp_prepexec handle OUTPUT , params , stmt
    [ , bound param ] [ , ...n ]
[ ; ]

Arguments

handle

The SQL Server-generated handle identifier. handle is a required parameter with an int return value.

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 isn't parameterized.

stmt

Defines the cursor result set. The stmt parameter is required and calls for an ntext, nchar, or nvarchar input value.

bound_param

Signifies the optional use of extra parameters. bound_param calls for an input value of any data type to designate the extra parameters in use.

Examples

The following example prepares and executes a simple statement:

Declare @Out int;
EXEC sp_prepexec @Out output,
    N'@P1 nvarchar(128), @P2 nvarchar(100)',
    N'SELECT database_id, name
      FROM sys.databases
      WHERE name=@P1 AND state_desc = @P2',
          @P1 = 'tempdb', @P2 = 'ONLINE';
EXEC sp_unprepare @Out;

Related content