Using Dynamic SQL in Stored Procedures

By Bill Graziano on 7 March 2011 | Tags: Queries , Dynamic SQL


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.


Related Articles

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

Aggregating Correlated Sub-Queries (23 October 2007)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

Returning Complex Data from User-Defined Functions with CROSS APPLY (11 June 2007)

Returning a week number for any given date and starting fiscal month (2 May 2007)

Passing an array of values to SQL Server (Stored Procedure) without parsing/string manipulation (26 March 2007)

Other Recent Forum Posts

Bulk alter SQL column data value in MS-SQL2019 in trans-sql (4h)

Bulk alter SQL column data value in MS-SQL2019 in a Trans-Sql (4h)

Filter query base on date field (4d)

Pull results from a stored procedure (4d)

Help With Query To Count Number of Pets & Vaccinations (5d)

Connect sql off network (5d)

Moving Registered Servers to a New Instance (9d)

CU 31 for 2017 Will Not Install (9d)

- Advertisement -