SQL - Dynamic SQL with Examples | Application Architect

Key Insights

Dynamic SQL allows you to construct and execute SQL statements at runtime, enabling flexible queries based on variable conditions, table names, or column selections that cannot be determined until execution time.
Use parameterized queries and proper input validation to prevent SQL injection attacks when building dynamic SQL—never concatenate user input directly into SQL strings.
Choose between EXECUTE IMMEDIATE for simple one-time executions and prepared statements for repeated executions with different parameters to optimize performance and security.

Understanding Dynamic SQL

Dynamic SQL refers to SQL statements that are constructed and executed at runtime rather than being hard-coded in your application. This approach becomes necessary when query structure depends on runtime conditions—such as varying WHERE clauses, dynamic table names, or user-selected columns.

The fundamental difference between static and dynamic SQL lies in compilation timing. Static SQL is parsed and optimized at compile time, while dynamic SQL undergoes this process during execution. This flexibility comes with trade-offs in performance, security, and complexity that you must manage carefully.

Basic Dynamic SQL Execution

Most database systems provide mechanisms to execute dynamic SQL. Here’s how different platforms handle basic execution:

SQL Server (T-SQL):

DECLARE @sql NVARCHAR(MAX);
DECLARE @tableName NVARCHAR(128) = 'Customers';

SET @sql = N'SELECT * FROM ' + QUOTENAME(@tableName);
EXEC sp_executesql @sql;

PostgreSQL:

DO $$
DECLARE
    sql_query TEXT;
    table_name TEXT := 'customers';
BEGIN
    sql_query := 'SELECT * FROM ' || quote_ident(table_name);
    EXECUTE sql_query;
END $$;

Oracle (PL/SQL):

DECLARE
    v_sql VARCHAR2(1000);
    v_table_name VARCHAR2(30) := 'CUSTOMERS';
BEGIN
    v_sql := 'SELECT * FROM ' || DBMS_ASSERT.SIMPLE_SQL_NAME(v_table_name);
    EXECUTE IMMEDIATE v_sql;
END;

Notice the use of QUOTENAME(), quote_ident(), and DBMS_ASSERT.SIMPLE_SQL_NAME(). These functions sanitize identifiers to prevent SQL injection through table or column names.

Parameterized Dynamic SQL

Parameterization is critical for security and performance. Parameters separate data from SQL structure, preventing injection attacks and enabling query plan reuse.

SQL Server with Parameters:

DECLARE @sql NVARCHAR(MAX);
DECLARE @minPrice DECIMAL(10,2) = 100.00;
DECLARE @category NVARCHAR(50) = 'Electronics';

SET @sql = N'
    SELECT ProductID, ProductName, Price
    FROM Products
    WHERE Price > @Price AND Category = @Cat';

EXEC sp_executesql 
    @sql,
    N'@Price DECIMAL(10,2), @Cat NVARCHAR(50)',
    @Price = @minPrice,
    @Cat = @category;

PostgreSQL with Parameters:

DO $$
DECLARE
    min_price NUMERIC := 100.00;
    category_name TEXT := 'Electronics';
BEGIN
    EXECUTE '
        SELECT product_id, product_name, price
        FROM products
        WHERE price > $1 AND category = $2'
    USING min_price, category_name;
END $$;

The parameter markers (@Price, @Cat in T-SQL; $1, $2 in PostgreSQL) ensure that values are treated as data, not executable code.

Building Dynamic WHERE Clauses

One of the most common use cases for dynamic SQL is constructing WHERE clauses based on optional filter criteria.

SQL Server Approach:

CREATE PROCEDURE SearchProducts
    @ProductName NVARCHAR(100) = NULL,
    @MinPrice DECIMAL(10,2) = NULL,
    @MaxPrice DECIMAL(10,2) = NULL,
    @Category NVARCHAR(50) = NULL
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM Products WHERE 1=1';
    DECLARE @params NVARCHAR(MAX) = N'@pName NVARCHAR(100), @pMinPrice DECIMAL(10,2), 
                                       @pMaxPrice DECIMAL(10,2), @pCategory NVARCHAR(50)';

    IF @ProductName IS NOT NULL
        SET @sql = @sql + N' AND ProductName LIKE ''%'' + @pName + ''%''';
    
    IF @MinPrice IS NOT NULL
        SET @sql = @sql + N' AND Price >= @pMinPrice';
    
    IF @MaxPrice IS NOT NULL
        SET @sql = @sql + N' AND Price <= @pMaxPrice';
    
    IF @Category IS NOT NULL
        SET @sql = @sql + N' AND Category = @pCategory';

    EXEC sp_executesql @sql, @params, 
        @pName = @ProductName,
        @pMinPrice = @MinPrice,
        @pMaxPrice = @MaxPrice,
        @pCategory = @Category;
END;

This pattern builds the WHERE clause incrementally based on which parameters are provided, maintaining parameterization throughout.

Dynamic Column Selection

When users need to select which columns to retrieve, dynamic SQL becomes necessary:

CREATE PROCEDURE GetCustomerData
    @IncludeEmail BIT = 0,
    @IncludePhone BIT = 0,
    @IncludeAddress BIT = 0
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'SELECT CustomerID, CustomerName';
    
    IF @IncludeEmail = 1
        SET @sql = @sql + N', Email';
    
    IF @IncludePhone = 1
        SET @sql = @sql + N', Phone';
    
    IF @IncludeAddress = 1
        SET @sql = @sql + N', Address, City, State, ZipCode';
    
    SET @sql = @sql + N' FROM Customers';
    
    EXEC sp_executesql @sql;
END;

Dynamic Sorting and Pagination

Implementing flexible sorting requires dynamic SQL when the sort column is determined at runtime:

CREATE PROCEDURE GetPagedProducts
    @PageNumber INT = 1,
    @PageSize INT = 20,
    @SortColumn NVARCHAR(50) = 'ProductName',
    @SortDirection NVARCHAR(4) = 'ASC'
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @offset INT = (@PageNumber - 1) * @PageSize;
    
    -- Whitelist allowed columns
    IF @SortColumn NOT IN ('ProductName', 'Price', 'Category', 'CreatedDate')
        SET @SortColumn = 'ProductName';
    
    IF @SortDirection NOT IN ('ASC', 'DESC')
        SET @SortDirection = 'ASC';
    
    SET @sql = N'
        SELECT ProductID, ProductName, Price, Category
        FROM Products
        ORDER BY ' + QUOTENAME(@SortColumn) + N' ' + @SortDirection + N'
        OFFSET @Off ROWS
        FETCH NEXT @Size ROWS ONLY';
    
    EXEC sp_executesql @sql, 
        N'@Off INT, @Size INT',
        @Off = @offset,
        @Size = @PageSize;
END;

The whitelist approach ensures only valid column names are used, preventing injection through the sort column parameter.

Handling Dynamic Table Names

When working with partitioned tables or multi-tenant systems, you may need dynamic table names:

CREATE PROCEDURE GetDataFromPartition
    @Year INT,
    @Month INT
AS
BEGIN
    DECLARE @tableName NVARCHAR(128);
    DECLARE @sql NVARCHAR(MAX);
    
    -- Construct table name
    SET @tableName = 'Sales_' + CAST(@Year AS NVARCHAR(4)) + '_' + 
                     RIGHT('0' + CAST(@Month AS NVARCHAR(2)), 2);
    
    -- Verify table exists
    IF OBJECT_ID(@tableName, 'U') IS NULL
    BEGIN
        RAISERROR('Table does not exist: %s', 16, 1, @tableName);
        RETURN;
    END;
    
    SET @sql = N'SELECT * FROM ' + QUOTENAME(@tableName) + 
               N' WHERE SaleDate >= @StartDate AND SaleDate < @EndDate';
    
    EXEC sp_executesql @sql,
        N'@StartDate DATE, @EndDate DATE',
        @StartDate = DATEFROMPARTS(@Year, @Month, 1),
        @EndDate = DATEADD(MONTH, 1, DATEFROMPARTS(@Year, @Month, 1));
END;

Returning Results from Dynamic SQL

Capturing results from dynamic SQL requires different techniques depending on your needs:

Using Temporary Tables:

CREATE TABLE #Results (
    ProductID INT,
    ProductName NVARCHAR(100),
    Price DECIMAL(10,2)
);

DECLARE @sql NVARCHAR(MAX) = N'
    INSERT INTO #Results
    SELECT ProductID, ProductName, Price
    FROM Products
    WHERE Category = @Cat';

EXEC sp_executesql @sql, N'@Cat NVARCHAR(50)', @Cat = 'Electronics';

SELECT * FROM #Results;
DROP TABLE #Results;

Using OUTPUT Parameters:

DECLARE @sql NVARCHAR(MAX);
DECLARE @totalSales DECIMAL(18,2);

SET @sql = N'SELECT @Total = SUM(Amount) FROM Sales WHERE Year = @Yr';

EXEC sp_executesql @sql,
    N'@Yr INT, @Total DECIMAL(18,2) OUTPUT',
    @Yr = 2024,
    @Total = @totalSales OUTPUT;

SELECT @totalSales AS TotalSales;

Performance Considerations

Dynamic SQL can impact performance due to query plan compilation overhead. Mitigate this by:

Using sp_executesql with parameters instead of EXEC() to enable plan reuse
Implementing parameter sniffing strategies when appropriate
Using OPTION (RECOMPILE) for queries with highly variable parameters
Caching frequently used dynamic SQL strings in application code

-- Force recompilation for volatile parameters
DECLARE @sql NVARCHAR(MAX) = N'
    SELECT * FROM Orders
    WHERE OrderDate >= @StartDate
    OPTION (RECOMPILE)';

EXEC sp_executesql @sql, N'@StartDate DATE', @StartDate = @inputDate;

Security Best Practices

Never trust user input. Always validate and sanitize:

Use parameterization for all data values
Whitelist valid identifiers (table names, column names)
Use built-in sanitization functions (QUOTENAME, quote_ident, etc.)
Validate data types before constructing SQL
Implement proper error handling to avoid information leakage
Log all dynamic SQL executions for audit purposes

Dynamic SQL is a powerful tool that solves real architectural challenges. Use it judiciously, prioritize security, and always consider whether static SQL with conditional logic might suffice before reaching for dynamic construction.