sp_prepare (Transact SQL)
Applies to: 
SQL Server
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
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.
Transact-SQL syntax conventions
Syntax
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
A. 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;
B. The following example prepares a statement in the AdventureWorks2016 database, and later executes it using the handle.
-- Prepare query
DECLARE @P1 INT;
EXEC sp_prepare @P1 OUTPUT,
N'@Param INT',
N'SELECT *
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Production.Product AS p ON sod.ProductID = p.ProductID
WHERE SalesOrderID = @Param
ORDER BY Style DESC;';
-- Return handle for calling application
SELECT @P1;
GO
Here is the result set.
-----------
1
(1 row affected)
Then the application executes the query twice using the handle value 1, before discarding the prepared plan.
EXEC sp_execute 1, 49879;
GO
EXEC sp_execute 1, 48766;
GO
EXEC sp_unprepare 1;
GO