Parameterizing Dynamic SQL statements

The prior post in this series was able to fix a parameterization issue by removing dynamic SQL and replacing it with a traditional SQL statement.  But sometimes that can’t be done because dynamic SQL is a requirement.  In that case the dynamic statement can still be parameterized using 2 optional parameters to the procedure sp_ExecuteSQL.

The first parameter of sp_ExecuteSQL is the only required parameter and contains the SQL statement to be executed.  What is less known is that this statement can reference variable names.  By using variable names instead of hard coded values the query execution plan used by that SQL statement is more likely to find a prior compiled plan and reuse it.

If the first parameter references a variable then 2 subsequent, optional parameters become required.  The second parameter declares the variables referenced in the SQL code.  Its format is a NVARCHAR variable that looks exactly like a declare statement only without the keyword declare.  The third parameter is potentially a series of parameters where each variable declared is set to a value.  Each assignment is separated by a comma.

Consider this example.

DECLARE @SQL NVARCHAR(4000);
SET @SQL = N'
SELECT TOP 10
a.CustomerName
, a.CreditLimit
, b.InvoiceDate
, b.DeliveryInstructions
FROM
Sales.Customers a
INNER JOIN
Sales.Invoices b ON a.CustomerID = b.BillToCustomerID
WHERE
CustomerName = @CustomerNameDynamic';

DECLARE @CustomerName NVARCHAR(200);
SET @CustomerName = N'Aakriti Byrraju'

EXEC sp_executeSQL @SQL
, N'@CustomerNameDynamic NVARCHAR(200)'
, @CustomerNameDynamic = @CustomerName;
GO 3

The first parameter, the SQL statement, references a variable, @CustomerNameDynamic.  The second parameter defines that variable with a data type.  The third parameter sets that previously defined variable to a value.

Since the @SQL variable remains constant the plan generated by this execution can be reused even if the value of @CustomerName is changed.

The following example shows how multiple parameters would be handled by adding an invoice date comparison to the existing customer name comparison.

DECLARE @SQL NVARCHAR(4000);
SET @SQL = 'SELECT TOP 10
a.CustomerName
, a.CreditLimit
, b.InvoiceDate
, b.DeliveryInstructions
FROM
Sales.Customers a
INNER JOIN
Sales.Invoices b ON a.CustomerID = b.BillToCustomerID
WHERE
CustomerName = @CustomerNameDynamic
AND
InvoiceDate = @InvoiceDateInside';

DECLARE @CustomerName NVARCHAR(200);
SET @CustomerName = N'Aakriti Byrraju'

DECLARE @InvoiceDate DATE;
SET @InvoiceDate = '1/1/2013'

EXEC sp_executeSQL @SQL
, N'@CustomerNameDynamic NVARCHAR(200), @InvoiceDateInside DATE'
, @CustomerNameDynamic = @CustomerName
, @InvoiceDateInside = @InvoiceDate;

 

Be careful when using dynamic SQL so that the plan cache is not flooded by plans that offer limited reuse by parameterizing the SQL it executes!