Dynamic Stored Procedures in SQL Server

Dynamic SQL allows stored procedures to “write” or dynamically generate their SQL statements. The most common use case for dynamic SQL is stored procedures with optional parameters in the WHERE clause. These are typically called from reports or screens that have multiple, optional search criteria. This article describes how to write these types of stored procedures so they execute well and resist SQL injection attacks.

A simple example of a stored procedure with dynamic SQL is:

use AdventureWorks
GO
IF  EXISTS (SELECT * FROM sys.objects 
   WHERE object_id = OBJECT_ID(N'[Sales].[GetSalesOrders]') 
   AND type in (N'P', N'PC'))
DROP PROCEDURE [Sales].[GetSalesOrders]
GO

CREATE PROCEDURE [Sales].[GetSalesOrders] (
 @CustomerID INT = NULL,
 @ContactID INT = NULL,
 @debug bit = 0 )
AS
SET NOCOUNT ON; 

DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);

SELECT @ParameterDefinition = '
 @CustomerParameter INT,
 @ContactParameter INT
';

SELECT @SQL = N'
SELECT [SalesOrderID], [OrderDate], [Status], 
 [CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE 1 = 1
';

IF @CustomerID IS NOT NULL
 SELECT @SQL = @SQL + N'
 AND CustomerID = @CustomerParameter ';
 
IF @ContactID IS NOT NULL
 SELECT @SQL = @SQL + N'
 AND ContactID = @ContactParameter ';
 
IF @debug = 1
 PRINT @SQL
 
EXEC sp_executeSQL 
 @SQL,
 @ParameterDefinition,
 @CustomerParameter = @CustomerID,
 @ContactParameter = @ContactID; 
GO

EXEC [Sales].[GetSalesOrders] @debug = 1, @CustomerID = 11724

This example uses the sp_executeSQL system stored procedure to execute the SQL.  This provides a fast, safe way to execute dynamic SQL.  It is also possible to use the EXECUTE statement to execute arbitrary strings that contain SQL statements.  I strongly encourage you to avoid this approach.  It may not perform as well and may leave you open to SQL injection attacks.

sp_executeSQL needs two Unicode strings and the parameter values passed to it.  The first Unicode string is the actual SQL statement.  We build this up based on the optional parameters passed into the stored procedure.  We only add the predicates to the WHERE clause that actually have values.  This is the part that handles the optional parameters.  The predicates we add use parameters rather than actual values at this point.  These will be parameterized SQL statements.

Also notice that this stored procedure uses a WHERE 1 = 1 to start the WHERE clause.  It’s a hack to shorten the stored procedure and saves writing code to determine if each predicate is the first predicate added.  It doesn’t affect the query plan of the generated SQL statement.
The second string holds every possible parameter that may appear in the dynamic SQL statement.  It defines the parameter and the data type.  This should hold every possible parameter regardless of whether they are actually used.

The sp_executeSQL statement is passed the generated SQL statement, the list of possible parameters and a mapping of those parameters to actual values.  This maps every parameter whether it was passed into the stored procedure or not.  The mapping is done by name rather than by order.  It ignores the parameters that aren’t found in the actual generated SQL statement.

Performance and Security

This stored procedure is generating parameterized SQL.  This makes it easier for SQL Server to reuse the query plan.
Due to the way we are passing and using parameters it is extremely difficult to attack this stored procedure using SQL injection.  All parameters are type checked as they are passed in.

Other Best Practices

I like to use different names for the parameters inside the generated SQL.  This helps me know exactly where each parameter is coming from.
I often use a debug parameter like you see here.  Tracking down issues with this type of stored procedure can be challenging and this makes it easier.  It has a negligible performance impact.
It’s best not to write code that accepts table and column names as parameters.  That is an easy approach for SQL injection to attack.  If that code must be written, pay careful attention to sanitizing those parameters before using them.