Using sp_prepare and sp_execute to utilize prepared SQL statements

By: Eric Blinn   |   Updated: 2022-12-12   |   Comments (1)   |   Related: > TSQL

Problem

I've heard people talk about prepared SQL with regards to SQL Server. What is prepared SQL? How does it work? What is the use case for it?

Solution

Prepared SQL allows a developer to write a parameterized SQL statement, store it in the SQL Server engine, and call it repeatedly. A prepared SQL statement is something like a small, temporary stored procedure.

This tip will walk through how to define and execute a prepared SQL statement. The code demos will be run with SSMS on SQL Server 2019 and against the WideWorldImporters sample database, which can be downloaded free from Github.

Defining a Prepared SQL Statement

Prepared SQL statements are defined using the sp_prepare stored procedure. Consider this simple query to return customer details.

SELECT CustomerName, AccountOpenedDate, StandardDiscountPercentage, PaymentDays FROM Sales.Customers WHERE CustomerID = 5;

This is the sort of query an application would likely call again and again for different customers. Storing it as a prepared SQL Statement can ensure that future executions are identical to this one without having to recreate the SQL each time.

Enter the sp_prepare stored procedure. Its job is to accept the SQL statement and a parameter list. They will be stored for future execution. It will return a number that refers to the saved query. This number will later be used to run the saved SQL statement.

The procedure accomplishes this via three parameters in this order:

1. Output parameter of type INT - the number used to identify the query.
2. A list of parameters defined within a Unicode string value.
3. SQL statement - may reference variables defined in the second parameter. It is also a Unicode string. The capital N values that precede the second and third parameters mean that they are Unicode strings. They are required.

Below is the same query as above, except it will be saved as a prepared statement for later execution.

DECLARE @PreparedStatementNumber INT;
EXEC sp_prepare @PreparedStatementNumber OUTPUT,
    N'@CustID INT',
    N'SELECT CustomerName, AccountOpenedDate, StandardDiscountPercentage, PaymentDays FROM Sales.Customers WHERE CustomerID = @CustID;';
 
SELECT @PreparedStatementNumber;

The results of this script will be two datasets:

1. An empty result set that matches the output of the query. It may be used by an application to build an object that can later hold the results of this query, and
2. An integer that will be used to call this prepared SQL statement. (For this demo, the value will be 1 since it will be the first and only statement prepared on this SPID.)

Calling a Prepared SQL Statement

Once a statement has been saved via sp_prepare, it can be executed using sp_execute. This procedure accepts a number parameter that coincides with the number that was assigned and returned when the query was prepared. After that, it will accept one value for each parameter that was defined. Since the query saved in the prior demo had only one parameter, there will only be one additional parameter to sp_execute.

This can lead to some very strange-looking procedure calls. The 1 is the prepared statement ID and the 5 is the parameter value for CustID.

EXEC sp_execute 1, 5;

The output of this sp_execute statement is identical to the output of the first query at the beginning of this tip.

The prepared query can be run many times with any valid parameter value for CustID.

EXEC sp_execute 1, 7;
EXEC sp_execute 1, 432;
EXEC sp_execute 1, 23;

Here is an example of a prepared SQL statement that has two parameters. Note: The second parameter of sp_prepare has two variables separated by commas. The sp_execute statement now has three parameters instead of two.

DECLARE @PreparedStatementNumber INT;
 
EXEC sp_prepare @PreparedStatementNumber OUTPUT,
    N'@CustID INT, @CustomerName NVARCHAR(100)',
    N'SELECT CustomerName, AccountOpenedDate, StandardDiscountPercentage, PaymentDays FROM Sales.Customers WHERE CustomerID = @CustID OR CustomerName LIKE @CustomerName;';
 
SELECT @PreparedStatementNumber;
 
EXEC sp_execute 5, 0, N'Tail%';

Destroying a Prepared SQL Statement

Like temporary tables, a process's entire cache of prepared statements is cleared if the SPID is disconnected. Rather than let this happen, a prepared statement can be cleared from this cache of statements using the sp_unprepare procedure.

This procedure accepts a single parameter, the number associated with the saved statement.

exec sp_unprepare 1;

Finding the Code Behind a Prepared SQL Statement

When performance tuning or debugging an application that uses prepared SQL, a trace or extended events session will be full of queries that look like this:

EXEC sp_execute 6, 7, 24, '1/1/2005';
EXEC sp_execute 13, 432, 'HELLO';
EXEC sp_execute 4, 'YELLOW', 'MEDIUM';

A common question about prepared SQL Statements is, "How do I know what queries they are running in this application?"

There is no DMV to query and no DBCC to call that will list the queries and their respective numbers. The only way to determine this is to run a trace that includes the SP:StmtCompleted object in profiler or its extended event equivalent sp_statement_completed.

Here is a screenshot of a profiler trace that was watching while the prepared SQL statement was executed in the earlier demo. Only the SP:StmtCompleted line has the actual statement.

Here is the output from an extended event session that was running at the same time as the profiler trace above. The output is the same.

Performance Concerns of Prepared SQL

Queries executed as prepared SQL statements are optimized differently than queries run as stored procedures or ad-hoc queries. On smaller datasets, this will not matter. On queries that search based on primary key columns, such as simple lookups like the very first demo in this tip, this also won't matter. When datasets get larger and queries incorporate more columns, this could lead to poor query performance that can be hard to resolve.

Alternatives to Prepared SQL

Prepared SQL was once a popular feature within SQL Server, but that is no longer the case. Nowadays, it is considered by many to be an antiquated way to write SQL code. It is not commonly taught in training classes nor written about in books and blogs.

It is far more popular to use stored procedures or even dynamic SQL via sp_executeSQL to implement queries that need to be called repeatedly.

Final Thoughts

The goal of this tip was not to encourage its readers to implement prepared SQL as a programming method. Instead, the purpose of this tip was to make sure the readers, as SQL Server professionals, understand this feature and how it works should they encounter it or a developer that wants to implement it.

Some applications programming languages attempt to use prepared statements by default. Sometimes a SQL Server professional inherits an application that uses prepared SQL. Such was the case for me; I inherited a 3^rd party application using prepared SQL that required a performance tuning effort. Many years into my career as a SQL Server professional, it was only then that I first saw a prepared SQL query and had to learn what that meant quickly.

Next Steps

• Getting Started with Stored Procedures
• Dynamic SQL Commands using sp_executeSQL
• SQL Server Prepared Statements vs Ad-Hoc Queries

About the author

Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips