Dynamic SQL: Writing and Executing Parameterized Queries at Runtime

Introduction

You're three weeks into a new project. The business wants a reporting stored procedure that filters sales data by region, date range, product category, and salesperson — but not all four at once. Sometimes they want just region and date. Sometimes just category. Sometimes all of them combined. You could write 16 separate procedures to cover every permutation, or you could write one elegant solution that builds the right query at runtime based on what parameters were actually passed.

That's the power of dynamic SQL. Instead of hardcoding query structure at compile time, you construct SQL strings programmatically and execute them at runtime. This lets you handle optional filters, variable table names, dynamic column lists, and conditionally applied logic — things that static SQL simply cannot accommodate gracefully. The tradeoff is that dynamic SQL is also one of the most dangerous tools in your SQL toolkit if used carelessly, and one of the most misunderstood in terms of performance.

By the end of this lesson, you'll know how to write dynamic SQL that is safe, readable, and genuinely useful — not just technically functional, but production-worthy. We'll work through realistic scenarios, from optional filter procedures to runtime pivot queries, and we'll be rigorous about security and performance throughout.

What you'll learn:

• How to construct and execute dynamic SQL strings using sp_executesql and EXEC in SQL Server (with PostgreSQL equivalents covered)
• How to use parameterization to prevent SQL injection while keeping queries flexible
• How to handle optional parameters and dynamic WHERE clause construction without writing a spaghetti mess
• How to use dynamic SQL for variable column lists, dynamic pivots, and runtime table selection
• When dynamic SQL is the right tool — and when it's a code smell you should refactor away

---

Prerequisites

You should be comfortable with:

• Writing multi-table JOINs and subqueries
• Creating and executing stored procedures with parameters
• Basic understanding of SQL injection as a concept
• Familiarity with string manipulation functions (CONCAT, +, string formatting)

This lesson uses SQL Server T-SQL as the primary dialect, with PostgreSQL equivalents shown where the syntax diverges meaningfully.

---

Why Dynamic SQL Exists (and Why Static SQL Falls Short)

Before writing a line of dynamic SQL, let's be precise about what problem we're actually solving.

Static SQL is SQL whose structure is fully determined at write time. The optimizer can parse it, compile it, and cache the execution plan. The columns, tables, and join conditions are fixed. This is the happy path — it's safe, fast, and predictable.

But real data systems have requirements that static SQL can't satisfy without painful workarounds:

Optional filters: A user can filter by zero to N criteria. With static SQL, you end up with hacks like WHERE (@region IS NULL OR region = @region). This "catch-all" pattern actually works for small tables, but it forces the optimizer into a corner — it can't generate an optimal plan because it doesn't know at compile time whether the filter will apply. For large tables, this causes significant performance degradation.

Variable column lists: You need to select different columns based on user configuration, or pivot rows into columns where the column names come from data.

Variable table names: Multi-tenant systems where each client has their own schema, or partition-by-year tables (sales_2022, sales_2023, sales_2024).

Conditional JOINs: Sometimes you need to join a supplemental table only when certain data conditions are met at runtime.

Dynamic SQL solves all of these — but it introduces new problems: SQL injection risk, plan cache pollution, and debugging complexity. Understanding the tradeoffs is what separates practitioners who use it well from those who create security vulnerabilities and performance nightmares.

---

The Two Execution Methods: EXEC vs sp_executesql

SQL Server gives you two ways to execute a dynamic SQL string. They are not equivalent, and choosing the wrong one has real consequences.

EXEC (or EXECUTE)

The simplest form: you build a string and execute it.

DECLARE @sql NVARCHAR(MAX);
DECLARE @region NVARCHAR(50) = 'Northeast';

SET @sql = N'SELECT order_id, customer_name, total_amount
             FROM dbo.orders
             WHERE region = ''' + @region + '''';

EXEC(@sql);

This works. It also has serious problems. Notice how we're embedding the variable value directly into the string with string concatenation. If @region contains '; DROP TABLE dbo.orders; --, you just got SQL-injected. This form offers no parameterization, no plan reuse (the SQL string changes every time a different region is passed), and no way to pass OUTPUT parameters.

Warning: Never use string concatenation to embed user-supplied values in a dynamic SQL string. This is the canonical SQL injection vulnerability. Use sp_executesql with parameters instead.

sp_executesql

This is the right tool for the job. It accepts a SQL string, a parameter definition string, and then the parameter values — exactly like a prepared statement.

DECLARE @sql NVARCHAR(MAX);
DECLARE @params NVARCHAR(500);
DECLARE @region NVARCHAR(50) = 'Northeast';

SET @sql = N'SELECT order_id, customer_name, total_amount
             FROM dbo.orders
             WHERE region = @region';

SET @params = N'@region NVARCHAR(50)';

EXEC sp_executesql @sql, @params, @region = @region;

The value of @region is passed as a proper parameter, not concatenated into the string. The SQL engine treats it as data, not as code. An attacker who passes '; DROP TABLE dbo.orders; -- as the region value will just get zero results back — their injection attempt is inert.

The other major benefit: the SQL string 'SELECT order_id... WHERE region = @region' is identical every time regardless of what value is passed. SQL Server can cache and reuse the execution plan, which is a significant performance win for high-frequency queries.

Tip: Make your dynamic SQL strings as stable as possible. The more the string itself varies, the less plan reuse you get. Parameters should carry values; the string should carry structure.

PostgreSQL Equivalent: EXECUTE with USING

In PostgreSQL PL/pgSQL, the equivalent pattern is:

DO $$
DECLARE
    v_region TEXT := 'Northeast';
    v_sql TEXT;
BEGIN
    v_sql := 'SELECT order_id, customer_name, total_amount
              FROM orders
              WHERE region = $1';
    
    EXECUTE v_sql USING v_region;
END;
$$;

The USING clause passes parameters positionally ($1, $2, etc.), achieving the same injection-safe parameterization as sp_executesql.

---

Building Optional Filters the Right Way

Now let's tackle the problem that sends most people to dynamic SQL in the first place: optional filter parameters.

Suppose you have a reporting procedure for an e-commerce platform. The business wants to be able to filter orders by any combination of:

• Date range (@start_date, @end_date)
• Customer region (@region)
• Product category (@category)
• Minimum order total (@min_total)

All parameters are optional. When NULL is passed, that filter should be omitted entirely.

Here's a production-quality approach:

CREATE OR ALTER PROCEDURE dbo.usp_GetFilteredOrders
    @start_date   DATE         = NULL,
    @end_date     DATE         = NULL,
    @region       NVARCHAR(50) = NULL,
    @category     NVARCHAR(100)= NULL,
    @min_total    DECIMAL(10,2)= NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql    NVARCHAR(MAX);
    DECLARE @params NVARCHAR(1000);

    SET @sql = N'
        SELECT 
            o.order_id,
            o.order_date,
            c.customer_name,
            c.region,
            p.category,
            o.total_amount
        FROM dbo.orders o
        INNER JOIN dbo.customers c ON o.customer_id = c.customer_id
        INNER JOIN dbo.order_items oi ON o.order_id = oi.order_id
        INNER JOIN dbo.products p ON oi.product_id = p.product_id
        WHERE 1 = 1';

    -- Append filters only when parameters are provided
    IF @start_date IS NOT NULL
        SET @sql = @sql + N' AND o.order_date >= @start_date';
    
    IF @end_date IS NOT NULL
        SET @sql = @sql + N' AND o.order_date <= @end_date';
    
    IF @region IS NOT NULL
        SET @sql = @sql + N' AND c.region = @region';
    
    IF @category IS NOT NULL
        SET @sql = @sql + N' AND p.category = @category';
    
    IF @min_total IS NOT NULL
        SET @sql = @sql + N' AND o.total_amount >= @min_total';

    SET @sql = @sql + N' ORDER BY o.order_date DESC;';

    -- Define ALL parameters (even ones that might not be used in this execution)
    SET @params = N'
        @start_date   DATE,
        @end_date     DATE,
        @region       NVARCHAR(50),
        @category     NVARCHAR(100),
        @min_total    DECIMAL(10,2)';

    EXEC sp_executesql @sql, @params,
        @start_date = @start_date,
        @end_date   = @end_date,
        @region     = @region,
        @category   = @category,
        @min_total  = @min_total;
END;

A few things worth highlighting in this design:

WHERE 1 = 1 is a useful trick. It gives you a valid WHERE clause to start with so that every conditional filter can use AND uniformly. Some developers find this aesthetically unpleasant — the optimizer ignores it completely, so it has zero runtime cost.

All parameters are declared even if unused. The @params string declares all five parameters regardless of which filters are actually applied in this particular execution. This is fine — sp_executesql will simply not use the ones not referenced in the SQL string.

Notice what's NOT concatenated: The filter values (@region, @start_date, etc.) never appear in the string directly. Only the SQL structure (AND c.region = @region) is concatenated. This is the key pattern to internalize.

Let's test it with a few calls:

-- Just date range
EXEC dbo.usp_GetFilteredOrders 
    @start_date = '2024-01-01', 
    @end_date = '2024-03-31';

-- Region and category only
EXEC dbo.usp_GetFilteredOrders 
    @region = 'Northeast', 
    @category = 'Electronics';

-- All filters
EXEC dbo.usp_GetFilteredOrders 
    @start_date = '2024-01-01',
    @end_date = '2024-12-31',
    @region = 'Northeast',
    @category = 'Electronics',
    @min_total = 500.00;

Each of these produces a different, minimal query — no unnecessary filter conditions, no catch-all WHERE clauses confusing the optimizer.

---

Dynamic Column Selection and Runtime Pivoting

Optional filters are just the beginning. Let's escalate to something genuinely hard with static SQL: a dynamic pivot.

The business wants a sales summary report that shows monthly totals as columns, but the number of months should dynamically reflect the data in the table — not be hardcoded. In 2024, they want 12 columns. When January 2025 data arrives, the report should automatically include a 13th column without code changes.

Here's how to build it:

CREATE OR ALTER PROCEDURE dbo.usp_MonthlySalesPivot
    @year INT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    -- Default to current year if not specified
    IF @year IS NULL SET @year = YEAR(GETDATE());

    DECLARE @col_list    NVARCHAR(MAX) = N'';
    DECLARE @select_list NVARCHAR(MAX) = N'';
    DECLARE @sql         NVARCHAR(MAX);
    DECLARE @params      NVARCHAR(100);

    -- Build column list from actual data
    -- This generates strings like: [2024-01],[2024-02],[2024-03]
    SELECT @col_list = @col_list + 
        QUOTENAME(FORMAT(DATEFROMPARTS(@year, month_num, 1), 'yyyy-MM')) + N','
    FROM (
        SELECT DISTINCT MONTH(order_date) AS month_num
        FROM dbo.orders
        WHERE YEAR(order_date) = @year
    ) AS months
    ORDER BY month_num;

    -- Remove trailing comma
    SET @col_list = LEFT(@col_list, LEN(@col_list) - 1);

    -- Build SELECT list with ISNULL to replace NULLs with 0
    SELECT @select_list = @select_list + 
        N'ISNULL(' + QUOTENAME(FORMAT(DATEFROMPARTS(@year, month_num, 1), 'yyyy-MM')) + N', 0) AS ' +
        QUOTENAME(FORMAT(DATEFROMPARTS(@year, month_num, 1), 'yyyy-MM')) + N','
    FROM (
        SELECT DISTINCT MONTH(order_date) AS month_num
        FROM dbo.orders
        WHERE YEAR(order_date) = @year
    ) AS months
    ORDER BY month_num;

    SET @select_list = LEFT(@select_list, LEN(@select_list) - 1);

    SET @sql = N'
        SELECT region, ' + @select_list + N'
        FROM (
            SELECT 
                c.region,
                FORMAT(o.order_date, ''yyyy-MM'') AS order_month,
                o.total_amount
            FROM dbo.orders o
            INNER JOIN dbo.customers c ON o.customer_id = c.customer_id
            WHERE YEAR(o.order_date) = @year
        ) AS source_data
        PIVOT (
            SUM(total_amount)
            FOR order_month IN (' + @col_list + N')
        ) AS pivot_result
        ORDER BY region;';

    SET @params = N'@year INT';

    EXEC sp_executesql @sql, @params, @year = @year;
END;

The column names ([2024-01], [2024-02]) must be embedded in the SQL string because they're structural — they're column identifiers, not values. They can't be parameterized. This is an important distinction: only values can be parameterized. Identifiers (column names, table names, schema names) must be embedded structurally.

But they're not coming from user input directly — they're derived from your own data. This is relatively safe. The real risk comes when identifiers come from user input, which is covered in the security section below.

Tip: QUOTENAME() is your best friend for identifiers. It wraps a value in square brackets and escapes any embedded brackets, preventing identifier injection. Always use it when embedding column or table names derived from data or user input.

---

Dynamic Table Names and Schema Selection

Multi-tenant architectures often store each client's data in separate schemas or databases. Runtime table selection is another classic dynamic SQL use case.

CREATE OR ALTER PROCEDURE dbo.usp_GetClientOrders
    @client_schema NVARCHAR(128),
    @start_date    DATE,
    @end_date      DATE
AS
BEGIN
    SET NOCOUNT ON;

    -- CRITICAL: Validate the schema name against a whitelist before using it
    IF NOT EXISTS (
        SELECT 1 FROM sys.schemas WHERE name = @client_schema
    )
    BEGIN
        RAISERROR('Invalid schema name: %s', 16, 1, @client_schema);
        RETURN;
    END;

    DECLARE @sql    NVARCHAR(MAX);
    DECLARE @params NVARCHAR(200);

    -- Safe to use QUOTENAME now that we've validated against sys.schemas
    SET @sql = N'
        SELECT order_id, order_date, total_amount
        FROM ' + QUOTENAME(@client_schema) + N'.orders
        WHERE order_date BETWEEN @start_date AND @end_date
        ORDER BY order_date DESC;';

    SET @params = N'@start_date DATE, @end_date DATE';

    EXEC sp_executesql @sql, @params,
        @start_date = @start_date,
        @end_date   = @end_date;
END;

The validation against sys.schemas is non-negotiable. The pattern is:

1. Receive the identifier as input
2. Validate it against a trusted source (system catalog, whitelist table, hardcoded list)
3. If valid, embed it using QUOTENAME()
4. Raise an error if invalid — don't silently ignore or try to sanitize it

Warning: Do not try to sanitize identifier inputs by stripping special characters. You'll either be too aggressive (breaking legitimate names) or miss an attack vector. Validate against a trusted list; anything else is insufficient.

---

Capturing Output from Dynamic SQL

You're not always just reading data. Sometimes you need to capture results or output values back into the calling scope.

Capturing Scalar Values with OUTPUT Parameters

CREATE OR ALTER PROCEDURE dbo.usp_GetTableRowCount
    @schema_name NVARCHAR(128),
    @table_name  NVARCHAR(128),
    @row_count   BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate both identifiers
    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = @schema_name 
          AND TABLE_NAME = @table_name
    )
    BEGIN
        RAISERROR('Table %s.%s does not exist.', 16, 1, @schema_name, @table_name);
        RETURN;
    END;

    DECLARE @sql    NVARCHAR(MAX);
    DECLARE @params NVARCHAR(200);

    SET @sql = N'SELECT @row_count = COUNT_BIG(*) FROM ' + 
               QUOTENAME(@schema_name) + N'.' + 
               QUOTENAME(@table_name) + N';';

    SET @params = N'@row_count BIGINT OUTPUT';

    EXEC sp_executesql @sql, @params, @row_count = @row_count OUTPUT;
END;

Calling it:

DECLARE @count BIGINT;

EXEC dbo.usp_GetTableRowCount 
    @schema_name = 'dbo',
    @table_name  = 'orders',
    @row_count   = @count OUTPUT;

SELECT @count AS order_count;

OUTPUT parameters work exactly as they do in regular stored procedures — you just declare them as OUTPUT in both the @params string and the EXEC call.

Capturing Resultsets into Temp Tables

When you need to process the results of a dynamic query, insert them into a temp table:

CREATE TABLE #order_summary (
    region       NVARCHAR(50),
    order_count  INT,
    total_revenue DECIMAL(14,2)
);

DECLARE @sql    NVARCHAR(MAX);
DECLARE @params NVARCHAR(200);
DECLARE @year   INT = 2024;

SET @sql = N'
    INSERT INTO #order_summary (region, order_count, total_revenue)
    SELECT 
        c.region,
        COUNT(o.order_id),
        SUM(o.total_amount)
    FROM dbo.orders o
    INNER JOIN dbo.customers c ON o.customer_id = c.customer_id
    WHERE YEAR(o.order_date) = @year
    GROUP BY c.region;';

SET @params = N'@year INT';

EXEC sp_executesql @sql, @params, @year = @year;

-- Now process the results in static SQL
SELECT region, order_count, total_revenue,
       total_revenue / SUM(total_revenue) OVER() AS pct_of_total
FROM #order_summary
ORDER BY total_revenue DESC;

DROP TABLE #order_summary;

Temp tables (but not table variables) are visible to dynamic SQL executed in the same session, which makes this a clean pattern for two-stage processing.

Tip: Table variables (DECLARE @t TABLE (...)) are not visible inside sp_executesql — that runs in a child scope. Use #temp_tables when you need to share state between your outer procedure and dynamic SQL.

---

Performance Considerations: Plan Caching and Parameter Sniffing

Dynamic SQL has a complicated relationship with SQL Server's plan cache, and you need to understand it to avoid surprises in production.

The Plan Cache Benefit of sp_executesql

When you use sp_executesql, SQL Server hashes the SQL string and checks if a plan already exists in the cache. If the string is identical to a previous execution, the cached plan is reused. This is why string stability matters so much.

Consider what happens with our optional filter procedure. When called with just @region, the SQL string looks like:

SELECT ... FROM dbo.orders o ... WHERE 1 = 1 AND c.region = @region ORDER BY o.order_date DESC;

When called with @region and @start_date, it looks like:

SELECT ... FROM dbo.orders o ... WHERE 1 = 1 AND o.order_date >= @start_date AND c.region = @region ORDER BY ...

These are structurally different strings, so they get different plan cache entries. That's actually correct behavior — these are meaningfully different queries that should have different execution plans. Dynamic SQL shines here because each structural variation gets its own optimized plan, unlike the catch-all (@region IS NULL OR region = @region) pattern which forces one plan to serve all cases.

Parameter Sniffing in Dynamic SQL

Parameter sniffing — where SQL Server compiles a plan based on the first parameter values it sees, which may be atypical — affects dynamic SQL the same way it affects regular queries.

If your dynamic procedure is first called with @region = 'Northeast' (which has 100,000 orders) and the optimizer chooses a full table scan as the optimal plan, that same plan will be reused for @region = 'Pacific' (which has 50 orders) — where an index seek would be far better.

The solution depends on the severity:

• OPTION (RECOMPILE) appended to your dynamic SQL forces recompilation every time — expensive for high-frequency queries, but appropriate for infrequent ad-hoc reporting.
• OPTION (OPTIMIZE FOR UNKNOWN) tells the optimizer to use average statistics rather than sniffed values — a reasonable middle ground.

SET @sql = @sql + N' ORDER BY o.order_date DESC OPTION (RECOMPILE);';

---

Security Deep Dive: What Safe Dynamic SQL Actually Looks Like

Let's be explicit about the rules, because "avoid SQL injection" is too vague to be actionable.

Rule 1: Values go through parameters, always. Anything that represents a data value (a filter value, a threshold, a date) must travel via the @params mechanism of sp_executesql. Never concatenate these.

Rule 2: Identifiers must be validated before embedding. Column names, table names, schema names cannot be parameterized — they're structural. Before embedding them, validate them against a trusted source:

• System catalogs (sys.schemas, sys.tables, INFORMATION_SCHEMA.COLUMNS)
• A configuration table you control
• A hardcoded list in application code

Rule 3: Always use QUOTENAME() on embedded identifiers. Even after validation, wrap identifiers in QUOTENAME(). This handles edge cases like names with spaces, reserved words, or unusual characters.

Rule 4: Least privilege applies to the executing context. The user executing the dynamic SQL should have only the permissions they need. Don't execute dynamic SQL under an elevated context unless necessary. Be especially careful with EXECUTE AS clauses that elevate permissions.

Rule 5: Log and audit dynamic SQL in sensitive contexts. If you're executing dynamic SQL that touches financial, PII, or sensitive data, log the constructed SQL string (before execution) to an audit table. This is invaluable for debugging and for security investigations.

-- Audit pattern
INSERT INTO dbo.dynamic_sql_audit (executed_by, executed_at, sql_text)
VALUES (SYSTEM_USER, GETUTCDATE(), @sql);

EXEC sp_executesql @sql, @params, ...;

---

Hands-On Exercise: Build a Flexible Data Export Procedure

Let's put everything together. Your task is to build a stored procedure called dbo.usp_ExportTableData that meets these requirements:

1. Accept a schema name, table name, and optional list of comma-separated column names
2. Accept optional filter criteria: a date column name, start date, and end date
3. If column names are provided, select only those columns; otherwise select all (*)
4. If a date filter is provided, apply it to the specified column
5. Return the results, ordered by the date column if provided, otherwise unordered
6. Validate all identifier inputs against system catalogs

Start by working through the requirements before looking at the solution below.

Think about:

• What needs to be validated and how?
• What's structural (must be embedded in the string) vs. what's a value (can be parameterized)?
• How will you handle the variable column list?

Here's a complete implementation:

CREATE OR ALTER PROCEDURE dbo.usp_ExportTableData
    @schema_name  NVARCHAR(128),
    @table_name   NVARCHAR(128),
    @columns      NVARCHAR(MAX)  = NULL,   -- Comma-separated column names, or NULL for all
    @date_column  NVARCHAR(128)  = NULL,   -- Column to filter/sort by
    @start_date   DATE           = NULL,
    @end_date     DATE           = NULL
AS
BEGIN
    SET NOCOUNT ON;

    -- Step 1: Validate table exists
    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = @table_name
    )
    BEGIN
        RAISERROR('Table [%s].[%s] does not exist or is not accessible.', 16, 1, 
                  @schema_name, @table_name);
        RETURN;
    END;

    -- Step 2: Validate date column if provided
    IF @date_column IS NOT NULL AND NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name 
          AND TABLE_NAME   = @table_name
          AND COLUMN_NAME  = @date_column
    )
    BEGIN
        RAISERROR('Column [%s] does not exist in [%s].[%s].', 16, 1,
                  @date_column, @schema_name, @table_name);
        RETURN;
    END;

    -- Step 3: Build and validate column list
    DECLARE @col_select NVARCHAR(MAX) = N'*';
    
    IF @columns IS NOT NULL
    BEGIN
        SET @col_select = N'';
        
        -- Split comma-separated column names and validate each
        SELECT @col_select = @col_select + QUOTENAME(LTRIM(RTRIM(value))) + N','
        FROM STRING_SPLIT(@columns, ',')
        WHERE EXISTS (
            SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = @schema_name
              AND TABLE_NAME   = @table_name
              AND COLUMN_NAME  = LTRIM(RTRIM(value))
        );
        
        IF LEN(@col_select) = 0
        BEGIN
            RAISERROR('None of the specified columns exist in the target table.', 16, 1);
            RETURN;
        END;
        
        -- Remove trailing comma
        SET @col_select = LEFT(@col_select, LEN(@col_select) - 1);
    END;

    -- Step 4: Build the SQL string
    DECLARE @sql    NVARCHAR(MAX);
    DECLARE @params NVARCHAR(200) = N'@start_date DATE, @end_date DATE';

    SET @sql = N'SELECT ' + @col_select + 
               N' FROM ' + QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name) +
               N' WHERE 1 = 1';

    -- Step 5: Apply date filter if requested (identifier embedded safely via validation + QUOTENAME)
    IF @date_column IS NOT NULL
    BEGIN
        IF @start_date IS NOT NULL
            SET @sql = @sql + N' AND ' + QUOTENAME(@date_column) + N' >= @start_date';
        
        IF @end_date IS NOT NULL
            SET @sql = @sql + N' AND ' + QUOTENAME(@date_column) + N' <= @end_date';

        SET @sql = @sql + N' ORDER BY ' + QUOTENAME(@date_column) + N' DESC';
    END;

    SET @sql = @sql + N';';

    -- Step 6: Execute
    EXEC sp_executesql @sql, @params,
        @start_date = @start_date,
        @end_date   = @end_date;
END;

Test calls to verify the behavior:

-- Export all columns from orders, no filter
EXEC dbo.usp_ExportTableData 
    @schema_name = 'dbo', 
    @table_name  = 'orders';

-- Export specific columns with date filter
EXEC dbo.usp_ExportTableData 
    @schema_name = 'dbo', 
    @table_name  = 'orders',
    @columns     = 'order_id, customer_id, total_amount, order_date',
    @date_column = 'order_date',
    @start_date  = '2024-01-01',
    @end_date    = '2024-06-30';

-- Attempt injection via table name (should raise error)
EXEC dbo.usp_ExportTableData 
    @schema_name = 'dbo', 
    @table_name  = 'orders; DROP TABLE dbo.orders; --';

The injection attempt in the third call fails at the validation step (INFORMATION_SCHEMA.TABLES won't find a table with that name), and an error is raised cleanly before any dynamic SQL is even constructed.

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting the N prefix on string literals

-- WRONG: Implicit conversion can cause issues with Unicode data
EXEC sp_executesql 'SELECT * FROM dbo.orders WHERE region = @region', 
                   '@region VARCHAR(50)', @region;

-- RIGHT: Always use N'' for NVARCHAR dynamic SQL
EXEC sp_executesql N'SELECT * FROM dbo.orders WHERE region = @region', 
                   N'@region NVARCHAR(50)', @region = @region;

If your table contains Unicode characters and you forget the N prefix, you'll get silent data truncation or character substitution. Make it a habit — every dynamic SQL string literal gets the N prefix.

Mistake 2: Mismatched parameter types

-- The procedure parameter is NVARCHAR(50)
-- But the @params declaration says VARCHAR(50)
-- Type mismatch can cause implicit conversions that hurt index usage
SET @params = N'@region VARCHAR(50)';  -- WRONG if column is NVARCHAR
SET @params = N'@region NVARCHAR(50)'; -- RIGHT

Check your column definitions in the table and match them in your parameter declarations. Type mismatches prevent index seeks.

Mistake 3: Debugging by printing, not running

When your dynamic SQL returns unexpected results or errors, print the constructed string before executing it:

-- Add this temporarily during development
PRINT @sql;
PRINT @params;
-- EXEC sp_executesql @sql, @params, ...;

Copy the printed SQL into a new query window, declare the parameters, and run it directly. This isolates whether the problem is in the string construction or the execution logic.

Mistake 4: Plan cache pollution with unstable strings

-- WRONG: The year value is embedded in the string
SET @sql = N'SELECT * FROM dbo.orders WHERE YEAR(order_date) = ' + CAST(@year AS NVARCHAR(4));

-- RIGHT: Pass the year as a parameter
SET @sql = N'SELECT * FROM dbo.orders WHERE YEAR(order_date) = @year';
SET @params = N'@year INT';
EXEC sp_executesql @sql, @params, @year = @year;

Every time a different year is passed, the first version generates a new plan cache entry. Pass it as a parameter. The plan cache entry is shared across all year values.

Mistake 5: Trying to use dynamic SQL for table-valued results in a function

Scalar and table-valued functions cannot execute dynamic SQL in SQL Server — sp_executesql requires a stored procedure or batch context. If you need dynamic logic in a function, refactor to a stored procedure that inserts into a temp table, or rethink the design.

Mistake 6: Nesting dynamic SQL unnecessarily

If you find yourself writing dynamic SQL that itself builds dynamic SQL strings, stop. You almost certainly have a design problem. Nested dynamic SQL is nearly impossible to debug, impossible to secure properly, and a nightmare for anyone who maintains the code after you.

---

When Not to Use Dynamic SQL

This is as important as knowing when to use it.

Don't use it for the catch-all WHERE clause pattern — then fail to actually use dynamic SQL. Many developers write WHERE (@region IS NULL OR region = @region) and call it "optional filtering." This is actually worse than dynamic SQL (the optimizer is confused) without the flexibility benefits. If you're going to handle optional filters, commit to dynamic SQL properly.

Don't use it when a well-designed static query will work. If your filtering requirements are genuinely fixed and the catch-all pattern performs adequately on your data volumes, static SQL is simpler and safer. Dynamic SQL has real complexity costs — harder to read, harder to debug, harder to review for security.

Don't use it to avoid learning JOINs or CTEs. Some developers reach for dynamic SQL to build queries that feel "too complicated" in static SQL. This is usually a sign that the query needs to be refactored, not dynamicized.

Don't use it for one-time queries. If you're writing a one-off data fix script, there's no reason to introduce dynamic SQL complexity.

The right mental model: dynamic SQL solves problems of structure — when the structure of the query itself needs to vary. If only the values vary, static SQL with parameters handles it better.

---

Summary & Next Steps

Dynamic SQL is a genuine superpower for data engineers and database developers — when it's applied with discipline. Let's crystallize what you've learned:

• sp_executesql is the only acceptable execution method when parameter values come from user input or application code. EXEC with concatenation is a SQL injection vulnerability waiting to happen.
• Values are parameterized; identifiers are validated then embedded. These are two different categories that require different handling.
• QUOTENAME() on all embedded identifiers protects against identifier injection and handles edge cases in naming.
• The WHERE 1 = 1 pattern with conditional AND appends is a clean, optimizer-friendly approach to optional filters.
• Temp tables bridge the scope gap between dynamic SQL execution and the calling context.
• Plan cache stability comes from stable strings — keep structure in the string and values in parameters.

Next Steps

Now that you can write safe, effective dynamic SQL, the natural next skills to build are:

• Execution plan analysis for dynamic SQL — learn to use sys.dm_exec_cached_plans and sys.dm_exec_sql_text to inspect what's actually in your plan cache and whether your dynamic queries are reusing plans as expected.
• Dynamic SQL for ETL pipelines — extend these patterns to build generic staging table loaders, data comparison scripts, and schema-driven transformation procedures.
• Row-level security with dynamic SQL — many RLS implementations use dynamic SQL to append security predicates based on the current user's role, which connects directly to what you've built here.
• Recursive and iterative dynamic SQL patterns — processing a list of tables or schemas in a loop, building up results across multiple dynamic executions.

The procedures you built in this lesson — particularly the flexible export procedure — are patterns you can adapt directly for production use. Start there, adapt them to your actual schema, and put them through their paces on real data volumes.