Edit

Share via

sp_prepare (Transact SQL)

  • Article

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

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

params

Identifies parameterized statements. params is a required OUTPUT parameter that calls for an ntext, nchar, or nvarchar input value. The params definition of variables is substituted for parameter markers in the statement. 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.

options

An optional parameter that returns a description of the cursor result set columns. options requires the following input value:

Value Description
0x0001 RETURN_METADATA

Examples

A. Prepare and execute a statement

The following example prepares and executes a basic Transact-SQL statement.

DECLARE @handle INT;

EXEC sp_prepare @handle 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 @handle,
    N'tempdb',
    N'ONLINE';

EXEC sp_unprepare @handle;

B. Prepare and execute a statement using the handle

The following example prepares a statement in the AdventureWorks2022 database, and later executes it using the handle.

-- Prepare query
DECLARE @handle INT;

EXEC sp_prepare @handle 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 @handle;
GO

Here's the result set.

1

Execute 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

Related content