sp_prepare (Transact SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Prepares a parameterized Transact-SQL statement and returns a statement handle for execution. sp_prepare is invoked by specifying ID = 11 in a tabular data stream (TDS) packet.

Topic link icon Transact-SQL Syntax Conventions

Syntax

-- Syntax for SQL Server, Azure SQL Data Warehouse, Parallel Data Warehouse  

sp_prepare handle OUTPUT, params, stmt, options  

Arguments

handle
Is a SQL Server-generated prepared 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.

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

Examples

The following example prepares and executes a simple statement.

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

Examples: Azure SQL Data Warehouse and Parallel Data Warehouse

The following example prepares and executes a simple statement.

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

See Also

System Stored Procedures (Transact-SQL)