Interesting Things about Prepared Statements and sp_executesql

Introduction

Despite the availability of alternatives like sp_prepare, sp_execute, and sp_prepexec, most T-SQL writers use sp_executesql to create reusable, and usually parameterized, entries in the plan cache.

The resulting entry in sys.dm_exec_cached_plans has objtype set to “Prepared” and points to an entry in the SQLCP cache store.

This article covers some less well-known details about prepared statements and sp_executesql.

Prepared Statements

One distinct feature of prepared statements is how they appear in the SQLMGR batch text cache, as revealed by sys.dm_exec_sql_text.

The parameter definitions are enclosed in parentheses and prefixed to the submitted text.

Let’s look at an example using any version of SQL Server and the AdventureWorks sample database. I’ll also create a temporary stored procedure to show some brief details about plan cache entries and the associated text:

IF OBJECT_ID(N'tempdb..#ShowPlans', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE #ShowPlans;
END;
GO
CREATE PROCEDURE #ShowPlans AS
    SET NOCOUNT, XACT_ABORT ON;
    SET STATISTICS XML OFF;

    SELECT
        P.usecounts,
        P.cacheobjtype,
        P.objtype,
        S.[text]
    FROM sys.dm_exec_cached_plans AS P
    CROSS APPLY sys.dm_exec_sql_text(P.plan_handle) AS S
    WHERE 
        S.[text] LIKE '%UniqueProducts%'
        AND S.[text] NOT LIKE N'%dm_exec_cached_plans%';
GO

The test code counts products matching a supplied name pattern:

DBCC FREEPROCCACHE;
GO
DECLARE 
    @Statement nvarchar(4000) =
        N'
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE @Search;
        ',
    @ParamDef nvarchar(4000) = 
        N'@Search nvarchar(50)';

EXECUTE sys.sp_executesql
    @stmt = @Statement,
    @params = @ParamDef,
    @Search = N'%';

EXECUTE dbo.#ShowPlans;

The output shows the results of the execution, the prepared plan, and the prefixed text:

The text is:

(@Search nvarchar(50))
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE @Search;
        

Plan reuse depends on the submitted text—including the prefixed parameter definition—matching exactly, including invisible elements like trailing spaces and CR/LF characters.

This is not normally a concern because the dynamic SQL is constructed the same way each time, or reused via a local variable. Still, it is a little less efficient than using sp_prepare or sp_prepexec because the entire text is submitted to the server each time rather than an integer handle (see my previous article for details).

Executing the parameterized form directly

If you copy the parameter-prefixed text above into an SSMS window and try to execute it, you’ll likely get error messages:

The syntax is indeed illegal when submitted this way, but if you’re careful to match the trailing spaces and line feeds you can match the cached text from a prior execution and receive an estimated execution plan.

The screenshot below shows the spaces with the SSMS setting ‘show white space’ enabled via the Edit/Advanced menu:

The estimated plan from successfully submitting that text in SSMS is:

If you try to get a post-execution (actual) plan, you will receive a different error:

Maybe this error message is one reason some people describe the cached plans created by sp_executesql as ‘anonymous procedures’. I don’t particularly like the terminology myself—they’re not procedures stored in the OBJCP cache store, they’re prepared statements in SQLCP. It is true they don’t have names though.

In any case, the problem here is we haven’t supplied a value for the @Search parameter, so execution isn’t possible. At first look, there doesn’t seem to be a way to do this from T-SQL. There is, but I need to take a brief detour first.

sp_executesql Named Parameters

The arguments to sp_executesql are a little unusual.

The documentation makes it seem like named parameters @stmt and @params are supported, but in fact you can name these two inputs any way you like—it’s only their presence and position that is important.

Any user-supplied parameter names do need to be correct (or not named, but positioned correctly).

The normal rules about all parameters after the first named one also needing to be named also apply.

Both the following work correctly in the running example:

-- Made-up parameter names
EXECUTE sys.sp_executesql
    @quack = @Statement,
    @oink = @ParamDef,
    @Search = N'%';

-- Second and all subsequent parameters named
EXECUTE sys.sp_executesql
    @Statement,
    @moo = @ParamDef,
    @Search = N'%';

This one does not:

-- Parameter #1 named, but #2 is not
EXECUTE sys.sp_executesql
    @moo = @Statement,
    @ParamDef,
    @Search = N'%';

The error is:

Supplying a Parameter Value

Back to the idea of successfully executing the unusual syntax from SSMS, for which we have so far only managed to get an estimated plan.

The @params parameter definition string is very similar to the parameter definitions used with a regular module like a stored procedure or function. This means you can specify OUTPUT of course, but also a default value in case the parameter is omitted.

This should allow us to provide a parameter value indirectly. Of course, we will still need to match some previously cached text, so let’s do that first:

DBCC FREEPROCCACHE;
GO
DECLARE 
    @Statement nvarchar(4000) =
        N'
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE @Search;
        ',
    @ParamDef nvarchar(4000) = 
        N'@Search nvarchar(50) = ''%'''; -- Default added

EXECUTE sys.sp_executesql
    @stmt = @Statement,
    @params = @ParamDef,
    @Search = N'%';

EXECUTE dbo.#ShowPlans;

Notice the @ParamDef value now has a default value specified. The script output shows the default is now part of the prefix:

We can now copy and paste the cached text exactly and successfully execute it in SSMS:

(@Search nvarchar(50) = '%')
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE @Search;
        

The estimated execution plan includes the parameter definition prefix:

The actual plan includes row counts, but omits the prefix:

This is an interesting experiment to show how things work under the covers, but it is of little practical use, as we shall see. A bit more detour first.

Passing NULL in @params

The documentation says this about the parameter definition passed as @params:

If the Transact-SQL statement or batch in @stmt doesn’t contain parameters, @params isn’t required. The default value for this parameter is NULL.

This is true, but omitting @params is not exactly the same as passing NULL.

Let’s try an unparameterized version of the query. Note that an untyped literal NULL cannot be directly passed in as @params. If you try that, you’ll get an error:

DBCC FREEPROCCACHE;
GO
DECLARE 
    @Statement nvarchar(4000) =
        N'
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE N''A%''
        ';

EXECUTE sys.sp_executesql
    @stmt = @Statement,
    @params = NULL; -- Will not work

EXECUTE dbo.#ShowPlans;

The error is:

You can’t use functions like CAST or CONVERT in a parameter value expression to specify a particular data type for the NULL. One workaround is to use a local variable to hold the NULL instead:

DBCC FREEPROCCACHE;
GO
DECLARE 
    @Statement nvarchar(4000) =
        N'
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE N''A%''
        ',
    @ParamDef nvarchar(4000) = NULL; -- Typed NULL

EXECUTE sys.sp_executesql
    @stmt = @Statement,
    @params = @ParamDef;

EXECUTE dbo.#ShowPlans;

Notice the plan is cached as adhoc instead of prepared because there are no parameters, but there are still empty parentheses prefixed to the batch text:

The same thing happens if you pass an empty string (N'') instead of NULL.

I won’t show the execution plan because it is an uninteresting seek finding the three product names beginning with ‘A’.

Omitting @params

Remember the documentation said that NULL was the default for @params.

Let’s see what happens if we omit the parameter instead of passing NULL or an empty string:

DBCC FREEPROCCACHE;
GO
DECLARE 
    @Statement nvarchar(4000) =
        N'
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE N''B%''
        ';

-- No @params
EXECUTE sys.sp_executesql
    @stmt = @Statement;

EXECUTE dbo.#ShowPlans;

The cached plan is still adhoc instead of prepared, but the empty parentheses prefix has disappeared from the batch text:

Plan Reuse without Parameters

As a consequence of mismatched batch text, omitting @params does not result in plan reuse when subsequent executions supply NULL or an empty string for @params. The empty parentheses really are important.

DBCC FREEPROCCACHE;
GO
DECLARE 
    @Statement nvarchar(4000) =
        N'
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE N''C%''
        ',
    @ParamDef nvarchar(4000) = NULL;

-- @params omitted
EXECUTE sys.sp_executesql
    @stmt = @Statement;

-- Empty string
EXECUTE sys.sp_executesql
    @stmt = @Statement,
    @params = N'';

-- Typed NULL
EXECUTE sys.sp_executesql
    @stmt = @Statement,
    @params = @ParamDef;

EXECUTE dbo.#ShowPlans;

The output shows that the omitted @params plan did not match with the others because it doesn’t have the prefixed empty parentheses. The plan for an empty @params string does match that for a supplied NULL or empty string, since both generate the prefix:

Separate plans cached with and without the empty prefix

Now, most of your sp_executesql calls will be parameterized, but if you use it without parameters be aware you’ll cache an adhoc plan and you have to be consistent with @params (omit, use NULL or empty string) to get plan reuse.

When you must omit @params

Incidentally, the prefixed empty parentheses are the reason you must omit @params if the submitted text is anything that must appear first in a batch.

For example, the following fails with errors:

DBCC FREEPROCCACHE;
GO
DECLARE 
    @Statement nvarchar(4000) =
        N'
        CREATE VIEW dbo.V 
        AS 
        SELECT n = 1;
        ',
    @ParamDef nvarchar(4000) = NULL;

-- NULL @params
EXECUTE sys.sp_executesql 
    @Statement, 
    @ParamDef;

-- Empty string @params
EXECUTE sys.sp_executesql 
    @Statement, 
    N'';

In both cases, the empty parentheses appear before CREATE VIEW in the batch text, which is not allowed.

Omitting @params allows the statement to execute successfully:

DBCC FREEPROCCACHE;
GO
DECLARE 
    @Statement nvarchar(4000) =
        N'
        CREATE VIEW dbo.V 
        AS 
        SELECT n = 1;
        ';

-- @params omitted
EXECUTE sys.sp_executesql 
    @Statement;

DROP VIEW dbo.V;

Plan Reuse with Parameters

Returning to the world of parameterized statements, I want to stress that it is the combination of @params and @stmt that determines the cached batch text. Though other cache keys must also match, having precisely matching batch text is a primary requirement for plan reuse.

To make the point, let’s look again at the original example, adding what we learned about executing the special text syntax from SSMS.

DBCC FREEPROCCACHE;
GO
DECLARE 
    -- Normal syntax, parameter definition separate
    @Statement1 nvarchar(4000) =
        N'
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE @Search;
        ',
    @ParamDef1 nvarchar(4000) = 
        N'@Search nvarchar(50) = N''D%''';

DECLARE
    -- Special syntax with a default
    @Statement2 nvarchar(4000) =
        N'(@Search nvarchar(50) = N''D%'')
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE @Search;
        ';

EXECUTE sys.sp_executesql
    @Statement1,
    @ParamDef1;

EXECUTE sys.sp_executesql
    @Statement2;

EXECUTE dbo.#ShowPlans;

Both sp_executesql statements produce the same batch text in different ways. The first example provides the parameter definition in @params as usual. The second one omits @params and prefixes @stmt with the definition inside parentheses instead.

The end result is the same, as confirmed by the test output showing successful plan use (usecounts = 2):

Usefulness

While interesting, the version using the special prefix syntax isn’t very useful:

(@Search nvarchar(50) = N''D%'')
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE @Search;

Remember, we need to supply a default value for this to execute. The problem is, there is no way to provide a different parameter value at runtime. You might think there is a way to work around this, but there isn’t.

If we try to supply any value for @params, that prefix will clash with the inlined (@Search nvarchar(50) = N''D%''), resulting in an error.

If we supply an empty string or NULL @params, the empty parentheses produced will still clash. Either way, we end up with a double prefix and an error.

If we omit @params—solving the empty parentheses issue—there is still no way to supply a parameter value. Any supplied value (even if it is named the same as a parameter in the statement) will be interpreted as a value for @params because of the argument’s position. This goes back to what I said about the slightly unusual behaviour of sp_executesql parameters.

For example, in this attempt the value supplied for @Search in the sp_executesql call is treated as if it were a @params specification:

DECLARE 
    @Statement nvarchar(4000) =
        N'(@Search nvarchar(50) = ''D%'')
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE @Search;
        ';

EXECUTE sys.sp_executesql
    @stmt = @Statement,
    @Search = N'E%';

Because the @Search part is in the @params position, the parser ends up seeing:

(E%)(@Search nvarchar(50) = 'D%')
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE @Search;

The extra prefix (E%) is simply a syntax error.

Cache Independence

With a few exceptions, the plan cache doesn’t much care how a plan got into cache. If a batch arrives that matches the stored text (and all other cache keys), plan reuse can occur.

The following version of the demo shows a plan cached by sp_prepare being reused by sp_executesql:

DBCC FREEPROCCACHE;
GO
DECLARE 
    @Handle integer,
    @Statement nvarchar(4000) =
        N'
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE @Search;
        ',
    @ParamDef nvarchar(4000) = 
        N'@Search nvarchar(50) = N''F%''';

-- Prepare the statement
EXECUTE sys.sp_prepare
    @Handle OUTPUT,
    @ParamDef,
    @Statement;

-- Unprepare it without calling sp_execute
EXECUTE sys.sp_unprepare
    @Handle;

-- Show the cached plan
EXECUTE dbo.#ShowPlans;

-- Execute with a different parameter value via sp_executesql
EXECUTE sys.sp_executesql
    @stmt = @Statement,
    @params = @ParamDef,
    @Search = N'G%';

-- Show the plan reuse
EXECUTE dbo.#ShowPlans;

The output is:

The result sets shown are:

1. An empty result from sp_prepare, showing the shape of the results the client should expect if sp_execute is called.
2. Details of the plan cached by sp_prepare.
3. The result from sp_executesql. There is one product name starting with ‘G’ (not ‘F’—that default parameter value is not used in this example).
4. The plan created by sp_prepare was successfully reused by sp_executesql (usecounts = 2).

Vice versa

Reuse can happen in the reverse direction as well. A plan cached by sp_executesql can be reused by a matching sp_prepare and sp_execute:

DBCC FREEPROCCACHE;
GO
DECLARE 
    @Statement nvarchar(4000) =
        N'
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE @Search;
        ',
    @ParamDef nvarchar(4000) = 
        N'@Search nvarchar(50) = N''H%''';

EXECUTE sys.sp_executesql
    @stmt = @Statement,
    @params = @ParamDef,
    @Search = N'I%';

EXECUTE dbo.#ShowPlans;

DECLARE
    @Handle integer = NULL;

EXECUTE sys.sp_prepare
    @Handle OUTPUT,
    @ParamDef,
    @Statement;

EXECUTE sys.sp_execute
    @Handle,
    @Search = N'J%';

EXECUTE sys.sp_unprepare
    @Handle;

EXECUTE dbo.#ShowPlans;

The output is:

Result sets:

1. There are 10 product names starting with ‘I’.
2. Plan cached by sp_executesql (use count = 1).
3. Empty shaping result from sp_prepare as usual.
4. sp_execute: There are no product names starting with ‘J’.
5. Plan reused twice, once by sp_prepare and once by sp_execute for a total use count of 3.

A DEFAULT Quirk

To finish up, I want to show a small bug when the keyword DEFAULT is specified for a prepared statement parameter’s default value.

DBCC FREEPROCCACHE;
GO
DECLARE 
    @Statement nvarchar(4000) =
        N'
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE @Search;
        ';

EXECUTE sys.sp_executesql
    @stmt = @Statement,
    @params = N'@Search nvarchar(50) = DEFAULT'; -- Note DEFAULT

EXECUTE dbo.#ShowPlans;

This variation executes correctly, but execution plan construction ends up reading uninitialized memory when it tries to retrieve the parameter’s runtime value for the Parameter List root node element:

It seems showplan construction is not correctly prepared to deal with the DEFAULT keyword. It probably expects a specifc value. I have no idea whether this represents a vulnerability or not.

I have opened a bug report.

That’s all for this article. Thank you for reading.