Day 22 of 32 Days of SQL Concepts — Dynamic SQL

Dynamic SQL represents one of the most powerful and perilous capabilities in modern data engineering. Unlike static SQL, where query structure remains fixed at compile time, dynamic SQL constructs query strings at runtime, enabling unprecedented flexibility in data manipulation and analysis. This flexibility, however, carries substantial risks if not carefully managed. As a data engineer with significant experience, you have likely encountered scenarios where dynamic SQL became indispensable, yet also situations where its misuse created maintenance nightmares and security vulnerabilities.

The fundamental appeal of dynamic SQL lies in its ability to handle scenarios that static SQL cannot elegantly address. When you must construct queries based on variable conditions, build parameterized filtering logic, or generate complex WHERE clauses dynamically, static SQL proves inadequate. Dynamic SQL fills this gap, enabling your applications to generate queries that adapt to runtime conditions.

However, this power demands respect. Dynamic SQL introduces complexity in query optimization, creates challenges in understanding execution plans, and opens potential security vulnerabilities if constructed carelessly. This exploration provides you with the knowledge necessary to harness dynamic SQL’s capabilities while maintaining code quality, security, and performance.

Foundations of Dynamic SQL

The Nature of Dynamic Query Construction

At its core, dynamic SQL involves constructing SQL statements as strings at runtime and then executing them. This differs fundamentally from static SQL, which the database engine parses, optimizes, and compiles once at connection time.

Consider this conceptual comparison:

Static SQL Execution Flow:

SQL String → Parse → Compile → Optimize → Execute → Results
(done once at compile time)

Dynamic SQL Execution Flow:

Build String → Execute String → Parse → Compile → Optimize → Execute → Results
(done each execution)

This distinction carries profound implications for performance, security, and maintainability.

When Dynamic SQL Becomes Necessary

Several scenarios make dynamic SQL the optimal or only viable approach:

Variable Column Selection: You may need to select different columns based on runtime conditions without knowing them beforehand.

DECLARE @ColumnList VARCHAR(MAX)

IF @RequestType = 'Financial'
    SET @ColumnList = 'CustomerID, Revenue, ProfitMargin, YearOverYearGrowth'
ELSE IF @RequestType = 'Demographic'
    SET @ColumnList = 'CustomerID, AgeGroup, Location, MaritalStatus'
ELSE
    SET @ColumnList = 'CustomerID, RegistrationDate, LastActivityDate'

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT ' + @ColumnList + ' FROM Customers WHERE Status = ''Active'''

EXEC sp_executesql @SQL

Conditional Joins: Building queries that require different JOIN operations based on runtime parameters.

DECLARE @IncludeOrderHistory BIT = 1
DECLARE @IncludeProductDetails BIT = 1

DECLARE @SQL NVARCHAR(MAX) = 'SELECT c.CustomerID, c.CustomerName FROM Customers c'

IF @IncludeOrderHistory = 1
    SET @SQL = @SQL + ' INNER JOIN Orders o ON c.CustomerID = o.CustomerID'

IF @IncludeProductDetails = 1
    SET @SQL = @SQL + ' INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
                        INNER JOIN Products p ON od.ProductID = p.ProductID'

EXEC sp_executesql @SQL

Table Selection: Scenarios where you dynamically determine which table to query.

DECLARE @DataSource VARCHAR(50) = 'CurrentYear'
DECLARE @TableName NVARCHAR(128)

SET @TableName = CASE 
    WHEN @DataSource = 'CurrentYear' THEN 'Sales2024'
    WHEN @DataSource = 'PreviousYear' THEN 'Sales2023'
    WHEN @DataSource = 'Archive' THEN 'SalesArchive'
    ELSE 'SalesLive'
END

DECLARE @SQL NVARCHAR(MAX) = 'SELECT TOP 1000 * FROM [' + @TableName + '] 
                               WHERE SalesAmount > 1000'

EXEC sp_executesql @SQL

Order By Clause Construction: Building dynamic sorting logic that responds to user input.

WHERE Clause Complexity: Constructing intricate WHERE clauses where conditions vary based on parameters.

SQL Server Dynamic SQL Implementation Patterns

Using sp_executesql for Parameterized Execution

The most critical best practice in dynamic SQL involves using sp_executesql rather than EXEC with concatenated strings. This stored procedure enables parameterized query execution, which provides both performance benefits and security advantages.

Basic sp_executesql Pattern

DECLARE @SQL NVARCHAR(MAX)
DECLARE @CustomerID INT = 42
DECLARE @MinAmount DECIMAL(10, 2) = 1000.00

SET @SQL = N'
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount,
        Status
    FROM Orders
    WHERE CustomerID = @CustID
        AND TotalAmount > @MinimumAmount
    ORDER BY OrderDate DESC
'

EXEC sp_executesql 
    @SQL,
    N'@CustID INT, @MinimumAmount DECIMAL(10, 2)',
    @CustID = @CustomerID,
    @MinimumAmount = @MinAmount

This pattern accomplishes several critical objectives. The @SQL variable contains the query structure with parameter placeholders rather than concatenated values. The second parameter to sp_executesql defines the parameter types, and the remaining parameters supply the values. This approach allows SQL Server to treat the query as if it were static, enabling plan caching and security hardening.

Advanced Parameter Passing

DECLARE @SQL NVARCHAR(MAX)
DECLARE @ProcedureName NVARCHAR(MAX)

-- Build a complex stored procedure call dynamically
DECLARE @ReportType VARCHAR(50) = 'SalesAnalysis'
DECLARE @StartDate DATE = '2024-01-01'
DECLARE @EndDate DATE = '2024-12-31'
DECLARE @RegionList VARCHAR(500) = 'North,South,East,West'

SET @ProcedureName = 'sp_Generate' + @ReportType + 'Report'

SET @SQL = N'EXEC ' + QUOTENAME(@ProcedureName) + 
           N' @StartDate = @SD, @EndDate = @ED, @Regions = @RegList'

EXEC sp_executesql 
    @SQL,
    N'@SD DATE, @ED DATE, @RegList VARCHAR(500)',
    @SD = @StartDate,
    @ED = @EndDate,
    @RegList = @RegionList

The QUOTENAME function protects against SQL injection by wrapping identifiers in brackets, escaping them properly if necessary.

Building Complex Filter Logic

One of the most common dynamic SQL patterns involves constructing WHERE clauses that adapt to multiple optional filters.

The Incremental WHERE Clause Pattern

CREATE PROCEDURE sp_SearchOrders
    @CustomerID INT = NULL,
    @OrderDateFrom DATE = NULL,
    @OrderDateTo DATE = NULL,
    @MinimumAmount DECIMAL(10, 2) = NULL,
    @MaximumAmount DECIMAL(10, 2) = NULL,
    @OrderStatus VARCHAR(50) = NULL,
    @SalesPersonID INT = NULL,
    @PageNumber INT = 1,
    @PageSize INT = 50
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @WhereClause NVARCHAR(MAX) = ''
    DECLARE @ParameterDefinition NVARCHAR(MAX)

    -- Build WHERE clause conditionally
    IF @CustomerID IS NOT NULL
        SET @WhereClause = @WhereClause + ' AND CustomerID = @CustID'

    IF @OrderDateFrom IS NOT NULL
        SET @WhereClause = @WhereClause + ' AND OrderDate >= @DateFrom'

    IF @OrderDateTo IS NOT NULL
        SET @WhereClause = @WhereClause + ' AND OrderDate <= @DateTo'

    IF @MinimumAmount IS NOT NULL
        SET @WhereClause = @WhereClause + ' AND TotalAmount >= @MinAmt'

    IF @MaximumAmount IS NOT NULL
        SET @WhereClause = @WhereClause + ' AND TotalAmount <= @MaxAmt'

    IF @OrderStatus IS NOT NULL
        SET @WhereClause = @WhereClause + ' AND [Status] = @Status'

    IF @SalesPersonID IS NOT NULL
        SET @WhereClause = @WhereClause + ' AND SalesPersonID = @SalesID'

    -- Remove leading AND if no conditions were added
    IF @WhereClause <> ''
        SET @WhereClause = ' WHERE 1=1 ' + @WhereClause
    ELSE
        SET @WhereClause = ''

    -- Calculate pagination
    DECLARE @SkipRows INT = (@PageNumber - 1) * @PageSize

    -- Build complete query
    SET @SQL = N'
        SELECT 
            OrderID,
            CustomerID,
            OrderDate,
            TotalAmount,
            [Status],
            SalesPersonID,
            ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS RowNumber,
            COUNT(*) OVER () AS TotalRecords
        FROM Orders ' + @WhereClause + N'
        ORDER BY OrderDate DESC
        OFFSET ' + CAST(@SkipRows AS NVARCHAR(10)) + N' ROWS
        FETCH NEXT ' + CAST(@PageSize AS NVARCHAR(10)) + N' ROWS ONLY
    '

    -- Define parameters
    SET @ParameterDefinition = N'
        @CustID INT,
        @DateFrom DATE,
        @DateTo DATE,
        @MinAmt DECIMAL(10, 2),
        @MaxAmt DECIMAL(10, 2),
        @Status VARCHAR(50),
        @SalesID INT
    '

    -- Execute with parameters
    EXEC sp_executesql 
        @SQL,
        @ParameterDefinition,
        @CustID = @CustomerID,
        @DateFrom = @OrderDateFrom,
        @DateTo = @OrderDateTo,
        @MinAmt = @MinimumAmount,
        @MaxAmt = @MaximumAmount,
        @Status = @OrderStatus,
        @SalesID = @SalesPersonID
END

This pattern demonstrates several important principles. The WHERE clause is constructed conditionally, only adding conditions when corresponding parameters are supplied. The OFFSET/FETCH clause is constructed dynamically for pagination. All values are passed as parameters to sp_executesql, preventing SQL injection attacks.

However, there exists a subtle issue with dynamic OFFSET/FETCH values. SQL Server requires these to be integer expressions or constants, not parameters. The pattern above converts them to strings and concatenates them, which is acceptable because they derive from application code, not user input.

Dynamic Column Selection and Sorting

CREATE PROCEDURE sp_GenerateCustomReport
    @SelectedColumns VARCHAR(MAX),
    @SortColumn VARCHAR(128) = 'OrderDate',
    @SortDirection VARCHAR(4) = 'DESC'
AS
BEGIN
    SET NOCOUNT ON

    -- Validate sort direction
    IF @SortDirection NOT IN ('ASC', 'DESC')
        SET @SortDirection = 'DESC'

    -- Validate sort column against a whitelist
    DECLARE @AllowedColumns TABLE (ColumnName VARCHAR(128))
    INSERT INTO @AllowedColumns (ColumnName)
    VALUES ('OrderID'), ('CustomerID'), ('OrderDate'), ('TotalAmount'), 
           ('Status'), ('ShippedDate'), ('DeliveredDate')

    IF NOT EXISTS (SELECT 1 FROM @AllowedColumns WHERE ColumnName = @SortColumn)
        SET @SortColumn = 'OrderDate'

    -- Validate and clean selected columns
    DECLARE @SafeColumnList VARCHAR(MAX) = ''
    DECLARE @Column VARCHAR(128)
    DECLARE @Position INT = 1

    WHILE CHARINDEX(',', @SelectedColumns, @Position) > 0
    BEGIN
        SET @Column = TRIM(SUBSTRING(
            @SelectedColumns, 
            @Position, 
            CHARINDEX(',', @SelectedColumns, @Position) - @Position
        ))

        IF EXISTS (SELECT 1 FROM @AllowedColumns WHERE ColumnName = @Column)
        BEGIN
            SET @SafeColumnList = @SafeColumnList + 
                CASE WHEN @SafeColumnList = '' THEN '' ELSE ',' END + 
                QUOTENAME(@Column)
        END

        SET @Position = CHARINDEX(',', @SelectedColumns, @Position) + 1
    END

    -- Handle final column
    SET @Column = TRIM(SUBSTRING(
        @SelectedColumns, 
        @Position, 
        LEN(@SelectedColumns)
    ))
    IF EXISTS (SELECT 1 FROM @AllowedColumns WHERE ColumnName = @Column)
        SET @SafeColumnList = @SafeColumnList + 
            CASE WHEN @SafeColumnList = '' THEN '' ELSE ',' END + 
            QUOTENAME(@Column)

    -- If no valid columns remain, use defaults
    IF @SafeColumnList = ''
        SET @SafeColumnList = 'OrderID, CustomerID, OrderDate, TotalAmount, [Status]'

    -- Build and execute query
    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT ' + @SafeColumnList + N'
        FROM Orders
        ORDER BY ' + QUOTENAME(@SortColumn) + N' ' + @SortDirection

    EXEC sp_executesql @SQL
END

This procedure demonstrates critical security practices for dynamic SQL. The selected columns are validated against a whitelist of allowed columns. Invalid columns are ignored rather than causing errors. The QUOTENAME function protects column names. The sort direction is validated against an explicit list of allowed values.

Dynamic JOIN Construction

CREATE PROCEDURE sp_QueryWithDynamicJoins
    @IncludeCustomerDetails BIT = 0,
    @IncludeShippingInfo BIT = 0,
    @IncludeProductDetails BIT = 0,
    @CustomerID INT = NULL
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @JoinClause NVARCHAR(MAX) = ''
    DECLARE @SelectClause NVARCHAR(MAX) = 'o.OrderID, o.OrderDate, o.TotalAmount'

    -- Base FROM clause
    DECLARE @FromClause NVARCHAR(MAX) = 'FROM Orders o'

    -- Add customer details
    IF @IncludeCustomerDetails = 1
    BEGIN
        SET @FromClause = @FromClause + 
            ' INNER JOIN Customers c ON o.CustomerID = c.CustomerID'
        SET @SelectClause = @SelectClause + 
            ', c.CustomerName, c.Email, c.Phone'
    END

    -- Add shipping information
    IF @IncludeShippingInfo = 1
    BEGIN
        SET @FromClause = @FromClause + 
            ' LEFT JOIN ShippingDetails s ON o.OrderID = s.OrderID'
        SET @SelectClause = @SelectClause + 
            ', s.ShipAddress, s.ShipCity, s.ShippedDate'
    END

    -- Add product details (requires OrderDetails table)
    IF @IncludeProductDetails = 1
    BEGIN
        SET @FromClause = @FromClause + 
            ' INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
              INNER JOIN Products p ON od.ProductID = p.ProductID'
        SET @SelectClause = @SelectClause + 
            ', p.ProductName, od.Quantity, od.UnitPrice'
    END

    -- Build WHERE clause
    DECLARE @WhereClause NVARCHAR(MAX) = ''
    IF @CustomerID IS NOT NULL
        SET @WhereClause = ' WHERE o.CustomerID = @CustID'

    -- Construct final SQL
    SET @SQL = N'SELECT ' + @SelectClause + ' ' + 
               @FromClause + @WhereClause + 
               ' ORDER BY o.OrderDate DESC'

    -- Execute
    EXEC sp_executesql 
        @SQL,
        N'@CustID INT',
        @CustID = @CustomerID
END

Dynamic Table Specification

CREATE PROCEDURE sp_QueryTableByName
    @SchemaName VARCHAR(128) = 'dbo',
    @TableName VARCHAR(128),
    @FilterColumn VARCHAR(128) = NULL,
    @FilterValue NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON

    -- Validate schema and table names against system catalog
    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = @SchemaName
          AND TABLE_NAME = @TableName
    )
    BEGIN
        RAISERROR('Invalid schema or table name', 16, 1)
        RETURN
    END

    -- Validate filter column if specified
    IF @FilterColumn IS NOT NULL
    BEGIN
        IF NOT EXISTS (
            SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = @SchemaName
              AND TABLE_NAME = @TableName
              AND COLUMN_NAME = @FilterColumn
        )
        BEGIN
            RAISERROR('Invalid filter column', 16, 1)
            RETURN
        END
    END

    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @WhereClause NVARCHAR(MAX) = ''

    -- Build WHERE clause if filter specified
    IF @FilterColumn IS NOT NULL AND @FilterValue IS NOT NULL
        SET @WhereClause = ' WHERE ' + QUOTENAME(@FilterColumn) + 
                          ' = @FilterVal'

    -- Build and execute query
    SET @SQL = N'SELECT * FROM ' + QUOTENAME(@SchemaName) + '.' + 
               QUOTENAME(@TableName) + @WhereClause

    EXEC sp_executesql 
        @SQL,
        N'@FilterVal NVARCHAR(MAX)',
        @FilterVal = @FilterValue
END

Security Implications and SQL Injection Prevention

Understanding SQL Injection Through Dynamic SQL

SQL injection represents the most critical vulnerability in dynamic SQL. Injection attacks occur when malicious input is incorporated into query strings without proper sanitization or parameterization.

Performance Considerations in Dynamic SQL

Query Plan Caching and Reuse

One significant advantage of using sp_executesql over EXEC with concatenated strings is query plan reuse. When you execute parameterized queries through sp_executesql, SQL Server caches execution plans and reuses them for subsequent invocations with different parameter values.

-- GOOD: Plan caching enabled
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Orders WHERE CustomerID = @CustID'
DECLARE @CustomerID INT = 42
EXEC sp_executesql @SQL, N'@CustID INT', @CustID = @CustomerID

-- Later execution with different parameter
SET @CustomerID = 100
EXEC sp_executesql @SQL, N'@CustID INT', @CustID = @CustomerID
-- SQL Server reuses the cached plan

-- POOR: Plan caching disabled
DECLARE @SQL2 NVARCHAR(MAX) = 'SELECT * FROM Orders WHERE CustomerID = 42'
EXEC (@SQL2)

DECLARE @SQL3 NVARCHAR(MAX) = 'SELECT * FROM Orders WHERE CustomerID = 100'
EXEC (@SQL3)
-- SQL Server compiles and caches two separate plans

This difference becomes critical when executing the same dynamic query hundreds or thousands of times with different parameters. Plan reuse eliminates repetitive compilation overhead.

Parameter Sniffing Challenges

However, dynamic SQL introduces a phenomenon called parameter sniffing, where the query optimizer chooses an execution plan based on the first parameter value encountered. If subsequent executions use significantly different parameter values that would benefit from a different plan, performance degradation occurs.

CREATE PROCEDURE sp_CustomerOrders
    @CustomerID INT
AS
BEGIN
    -- If first execution uses CustomerID with 1000 orders,
    -- optimizer may choose a plan optimized for large result sets.
    -- Subsequent executions with CustomerID having 2 orders
    -- may perform poorly with that plan.

    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT o.OrderID, o.OrderDate, o.TotalAmount
        FROM Orders o
        WHERE o.CustomerID = @CustID
    '

    EXEC sp_executesql 
        @SQL,
        N'@CustID INT',
        @CustID = @CustomerID
END

-- First execution with customer having many orders
EXEC sp_CustomerOrders @CustomerID = 1  -- 5000 orders

-- Second execution with customer having few orders
EXEC sp_CustomerOrders @CustomerID = 999999  -- 3 orders
-- May use suboptimal plan

Mitigation strategies include:

-- Strategy 1: RECOMPILE hint forces new plan each execution
CREATE PROCEDURE sp_CustomerOrdersRecompile
    @CustomerID INT
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT o.OrderID, o.OrderDate, o.TotalAmount
        FROM Orders o
        WHERE o.CustomerID = @CustID
        OPTION (RECOMPILE)
    '

    EXEC sp_executesql 
        @SQL,
        N'@CustID INT',
        @CustID = @CustomerID
END

-- Strategy 2: Local variable prevents sniffing
CREATE PROCEDURE sp_CustomerOrdersLocalVar
    @CustomerID INT
AS
BEGIN
    DECLARE @LocalCustomerID INT = @CustomerID

    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT o.OrderID, o.OrderDate, o.TotalAmount
        FROM Orders o
        WHERE o.CustomerID = @LocalCustID
    '

    EXEC sp_executesql 
        @SQL,
        N'@LocalCustID INT',
        @LocalCustID = @LocalCustomerID
END

-- Strategy 3: OPTIMIZE FOR hint
CREATE PROCEDURE sp_CustomerOrdersOptimizeFor
    @CustomerID INT
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT o.OrderID, o.OrderDate, o.TotalAmount
        FROM Orders o
        WHERE o.CustomerID = @CustID
        OPTION (OPTIMIZE FOR (@CustID = 500))
    '

    EXEC sp_executesql 
        @SQL,
        N'@CustID INT',
        @CustID = @CustomerID
END

Execution Plan Analysis

Dynamic SQL can make execution plan analysis challenging because the same procedure may generate different plans depending on runtime conditions.

-- This stored procedure generates different execution plans
-- depending on the @ReportType parameter

CREATE PROCEDURE sp_DynamicReport
    @ReportType VARCHAR(50)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)

    IF @ReportType = 'Summary'
        SET @SQL = N'
            SELECT 
                YEAR(OrderDate) AS [Year],
                MONTH(OrderDate) AS [Month],
                COUNT(*) AS OrderCount,
                SUM(TotalAmount) AS TotalRevenue
            FROM Orders
            GROUP BY YEAR(OrderDate), MONTH(OrderDate)
        '
    ELSE IF @ReportType = 'Detail'
        SET @SQL = N'
            SELECT 
                o.OrderID,
                c.CustomerName,
                o.OrderDate,
                o.TotalAmount,
                p.ProductName,
                od.Quantity
            FROM Orders o
            INNER JOIN Customers c ON o.CustomerID = c.CustomerID
            INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
            INNER JOIN Products p ON od.ProductID = p.ProductID
        '
    ELSE
        SET @SQL = N'
            SELECT 
                CustomerID,
                COUNT(*) AS OrderCount,
                SUM(TotalAmount) AS TotalRevenue,
                AVG(TotalAmount) AS AvgOrderValue
            FROM Orders
            GROUP BY CustomerID
            HAVING COUNT(*) > 5
        '

    EXEC sp_executesql @SQL
END

Analyzing such procedures requires understanding the decision logic and examining plans for each potential code path.

Complexity of Query Optimization

Dynamic SQL introduces additional optimization complexity because the optimizer must handle query structure that is not known until runtime.

-- This dynamic query's optimization difficulty varies
CREATE PROCEDURE sp_ComplexDynamicSearch
    @FilterByDate BIT = 0,
    @FilterByAmount BIT = 0,
    @FilterByStatus BIT = 0,
    @StartDate DATE = NULL,
    @EndDate DATE = NULL,
    @MinAmount DECIMAL(10, 2) = NULL,
    @MaxAmount DECIMAL(10, 2) = NULL,
    @Status VARCHAR(50) = NULL
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT 
            o.OrderID,
            o.CustomerID,
            o.OrderDate,
            o.TotalAmount,
            o.[Status],
            (SELECT COUNT(*) FROM OrderDetails od 
             WHERE od.OrderID = o.OrderID) AS LineItems
        FROM Orders o
        WHERE 1=1
    '

    IF @FilterByDate = 1
        SET @SQL = @SQL + N' AND o.OrderDate BETWEEN @SD AND @ED'

    IF @FilterByAmount = 1
        SET @SQL = @SQL + N' AND o.TotalAmount BETWEEN @MinAmt AND @MaxAmt'

    IF @FilterByStatus = 1
        SET @SQL = @SQL + N' AND o.[Status] = @Stat'

    SET @SQL = @SQL + N' ORDER BY o.OrderDate DESC'

    -- Multiple parameter definitions depending on filters
    DECLARE @ParamDef NVARCHAR(MAX) = N'
        @SD DATE, @ED DATE, @MinAmt DECIMAL(10,2), @MaxAmt DECIMAL(10,2), @Stat VARCHAR(50)
    '

    EXEC sp_executesql 
        @SQL,
        @ParamDef,
        @SD = @StartDate,
        @ED = @EndDate,
        @MinAmt = @MinAmount,
        @MaxAmt = @MaxAmount,
        @Stat = @Status
END

The optimizer must choose execution strategies without knowing which filters will be active. This can result in plans that are suboptimal for some parameter combinations.

Advanced Dynamic SQL Patterns

Building Bulk Operations Dynamically

CREATE PROCEDURE sp_BulkUpdateByCondition
    @SchemaName VARCHAR(128) = 'dbo',
    @TableName VARCHAR(128),
    @UpdateColumn VARCHAR(128),
    @UpdateValue NVARCHAR(MAX),
    @FilterColumn VARCHAR(128),
    @FilterValue NVARCHAR(MAX),
    @AffectedRows INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    -- Validate against system catalog
    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = @SchemaName
          AND TABLE_NAME = @TableName
    )
    BEGIN
        RAISERROR('Table does not exist', 16, 1)
        RETURN
    END

    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = @SchemaName
          AND TABLE_NAME = @TableName
          AND COLUMN_NAME IN (@UpdateColumn, @FilterColumn)
    )
    BEGIN
        RAISERROR('One or more columns do not exist', 16, 1)
        RETURN
    END

    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = N'
        UPDATE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + N'
        SET ' + QUOTENAME(@UpdateColumn) + N' = @UpdateVal
        WHERE ' + QUOTENAME(@FilterColumn) + N' = @FilterVal
    '

    EXEC sp_executesql 
        @SQL,
        N'@UpdateVal NVARCHAR(MAX), @FilterVal NVARCHAR(MAX)',
        @UpdateVal = @UpdateValue,
        @FilterVal = @FilterValue

    SET @AffectedRows = @@ROWCOUNT
END

Dynamic PIVOT Operations

CREATE PROCEDURE sp_DynamicPivot
    @PivotColumn VARCHAR(128) = 'OrderMonth',
    @AggregateColumn VARCHAR(128) = 'TotalAmount',
    @AggregateFunction VARCHAR(50) = 'SUM'
AS
BEGIN
    SET NOCOUNT ON

    -- Validate aggregate function
    IF @AggregateFunction NOT IN ('SUM', 'AVG', 'COUNT', 'MIN', 'MAX')
    BEGIN
        RAISERROR('Invalid aggregate function', 16, 1)
        RETURN
    END

    -- Get distinct pivot values
    DECLARE @PivotValues NVARCHAR(MAX)

    SET @PivotValues = STUFF(
        (
            SELECT DISTINCT ',' + QUOTENAME(CAST([Year] AS VARCHAR(4)) + '-' + 
                                   CAST([Month] AS VARCHAR(2)))
            FROM (
                SELECT 
                    YEAR(OrderDate) AS [Year],
                    MONTH(OrderDate) AS [Month]
                FROM Orders
            ) AS MonthYears
            FOR XML PATH('')
        ),
        1,
        1,
        ''
    )

    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT 
            CustomerID,
            ' + @PivotValues + N'
        FROM (
            SELECT 
                o.CustomerID,
                YEAR(o.OrderDate) AS [Year],
                MONTH(o.OrderDate) AS [Month],
                o.TotalAmount
            FROM Orders o
        ) AS SourceData
        PIVOT (
            ' + @AggregateFunction + N'(TotalAmount)
            FOR CONCAT([Year], ''-'', [Month]) IN (' + @PivotValues + N')
        ) AS PivotResult
    '

    EXEC sp_executesql @SQL
END

Dynamic Scheduling and Conditional Execution

CREATE PROCEDURE sp_ConditionalDataLoad
    @DataSource VARCHAR(50),
    @TargetEnvironment VARCHAR(50) = 'Dev'
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @LinkedServer NVARCHAR(128)
    DECLARE @SourceDatabase NVARCHAR(128)

    -- Determine connection string based on environment
    SELECT 
        @LinkedServer = CASE 
            WHEN @TargetEnvironment = 'Prod' THEN 'PROD_SERVER'
            WHEN @TargetEnvironment = 'Stage' THEN 'STAGE_SERVER'
            ELSE 'DEV_SERVER'
        END,
        @SourceDatabase = CASE 
            WHEN @DataSource = 'Legacy' THEN 'LegacyDB'
            WHEN @DataSource = 'Modern' THEN 'ModernDB'
            ELSE 'DefaultDB'
        END

    -- Build dynamic linked server query
    SET @SQL = N'
        INSERT INTO dbo.CustomerData (CustomerID, CustomerName, Email)
        SELECT 
            c.CustomerID,
            c.Name,
            c.Email
        FROM OPENQUERY(
            ' + QUOTENAME(@LinkedServer) + N',
            ''SELECT CustomerID, Name, Email FROM ' + @SourceDatabase + N'.dbo.Customers 
              WHERE IsActive = 1''
        ) AS c
        WHERE NOT EXISTS (
            SELECT 1 FROM dbo.CustomerData cd
            WHERE cd.CustomerID = c.CustomerID
        )
    '

    EXEC sp_executesql @SQL

    DECLARE @RowsLoaded INT = @@ROWCOUNT

    -- Log the operation
    INSERT INTO dbo.DataLoadLog (LoadDate, SourceSystem, TargetEnvironment, RecordsLoaded)
    VALUES (GETDATE(), @DataSource, @TargetEnvironment, @RowsLoaded)
END

Error Handling in Dynamic SQL

CREATE PROCEDURE sp_SafeDynamicOperation
    @OperationType VARCHAR(50),
    @TableName VARCHAR(128),
    @ColumnName VARCHAR(128),
    @Value NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @ErrorMessage NVARCHAR(MAX)
    DECLARE @ErrorNumber INT
    DECLARE @ErrorSeverity INT
    DECLARE @ErrorState INT

    BEGIN TRY
        -- Validate identifiers
        IF NOT EXISTS (
            SELECT 1 FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME = @TableName
        )
            THROW 50001, 'Table does not exist', 1

        IF NOT EXISTS (
            SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName
        )
            THROW 50002, 'Column does not exist', 1

        -- Build query based on operation type
        IF @OperationType = 'UPDATE'
        BEGIN
            SET @SQL = N'
                UPDATE ' + QUOTENAME(@TableName) + N'
                SET ' + QUOTENAME(@ColumnName) + N' = @Val
                WHERE ' + QUOTENAME(@ColumnName) + N' IS NOT NULL
            '
        END
        ELSE IF @OperationType = 'DELETE'
        BEGIN
            SET @SQL = N'
                DELETE FROM ' + QUOTENAME(@TableName) + N'
                WHERE ' + QUOTENAME(@ColumnName) + N' = @Val
            '
        END
        ELSE
        BEGIN
            THROW 50003, 'Invalid operation type', 1
        END

        -- Execute with error handling
        EXEC sp_executesql 
            @SQL,
            N'@Val NVARCHAR(MAX)',
            @Val = @Value

        DECLARE @AffectedRows INT = @@ROWCOUNT

        SELECT 
            'Success' AS OperationStatus,
            @AffectedRows AS RowsAffected

    END TRY
    BEGIN CATCH
        SELECT 
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage

        -- Log error details
        INSERT INTO dbo.ErrorLog 
            (ErrorNumber, ErrorSeverity, ErrorState, ErrorMessage, SQL, ExecutionTime)
        VALUES 
            (ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE(), @SQL, GETDATE())

        THROW
    END CATCH
END

Best Practices and Design Principles

The Security Hierarchy

When implementing dynamic SQL, observe this priority hierarchy:

Priority 1: Use Static SQL Always prefer static SQL when possible. If your query structure is known at design time, use static SQL without compromise.

Priority 2: Use Parameterized Dynamic SQL (sp_executesql) If dynamic SQL is necessary, use sp_executesql with all values passed as parameters. This provides plan caching and injection protection.

Priority 3: Validate and Whitelist Identifiers For identifiers that must be dynamic (table names, column names), validate against system catalog or a whitelist. Use QUOTENAME to protect them.

Priority 4: Input Validation For values that must be concatenated (such as sort directions or table names from whitelists), validate rigorously before concatenation.

Priority 5: Never Trust User Input in Dynamic SQL Never directly concatenate user input into dynamic SQL under any circumstances.

Documentation Requirements

Dynamic SQL procedures demand thorough documentation:

CREATE PROCEDURE sp_ExampleWithDocumentation
    @Parameter1 INT,
    @Parameter2 VARCHAR(50)
AS
/*
PROCEDURE DOCUMENTATION
=======================
Name:           sp_ExampleWithDocumentation
Author:         Data Engineering Team
Date Created:   2024-01-15
Last Modified:  2024-06-09

Purpose:
    Demonstrates best practices for documenting dynamic SQL procedures.
    This procedure constructs queries dynamically based on input parameters
    to support flexible filtering and reporting.

Parameters:
    @Parameter1     INT     - Customer identifier for filtering
    @Parameter2     VARCHAR(50) - Report type (Summary, Detail, Custom)

Returns:
    Result set containing customer orders with variable details based on report type

Security:
    All user inputs are passed as parameters to sp_executesql
    Identifiers are validated against system catalog
    No direct string concatenation of user input
    Whitelist validation for report type

Performance Considerations:
    Query plans are cached when sp_executesql is used
    Parameter sniffing may affect certain parameter combinations
    Recommend adding RECOMPILE hint if parameter distribution is highly variable

Example Usage:
    EXEC sp_ExampleWithDocumentation @Parameter1 = 42, @Parameter2 = 'Summary'

Modifications Log:
    2024-06-09 - Added parameter validation check
    2024-05-15 - Optimized JOIN order for large customer sets
    2024-01-15 - Initial creation
*/
BEGIN
    SET NOCOUNT ON

    -- Implementation
END

Conclusion

Dynamic SQL represents a powerful capability that, when wielded carefully, enables sophisticated data manipulation scenarios that static SQL cannot address. The fundamental principle guiding all dynamic SQL work must be security first. Every dynamic query should use parameterized execution through sp_executesql, every identifier should be validated and quoted, and every user input should be treated as potentially hostile.

The performance implications of dynamic SQL demand attention to plan caching, parameter sniffing, and query optimization. Understanding how your database engine handles dynamic queries allows you to write procedures that perform well across varying parameter combinations. When you do employ dynamic SQL, employ it with discipline, documentation, and comprehensive security measures.

The investment in understanding dynamic SQL’s nuances, pitfalls, and best practices will continue to serve you throughout your career, enabling you to build robust, secure, and performant data solutions.