Using Dynamic SQL in Stored Procedures

-- # 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;