Export (0) Print
Expand All

sp_prepexec (Transact-SQL)

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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

Topic link icon Transact-SQL Syntax Conventions

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

handle

Is 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 is not 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 additional parameters. bound_param calls for an input value of any data type to designate the additional parameters in use.

The following example prepares and executes a simple statement.

Declare @P1 int;
EXEC sp_prepexec @P1 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 @P1;
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft