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.

-- # Using Dynamic SQL in Stored Procedures
--
-- 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.
--
-- 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.
--
-- https://www.sqlteam.com/articles/using-dynamic-sql-in-stored-procedures

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;