Dynamic SQL in Relational Databases
Overview
Dynamic SQL refers to the technique of constructing and executing SQL query strings programmatically at runtime rather than writing static, pre-compiled SQL statements. While static SQL is preferred for standard application transactions due to compile-time verification and guaranteed parameterization, dynamic SQL is indispensable when query structures cannot be predetermined—such as dynamic filtering dashboards with 20 optional inputs, dynamic multi-tenant table routing, automated partition maintenance, and dynamic pivoting.
What You Will Learn
In this lesson, you will master:
- Static vs Dynamic SQL: Understanding compilation, plan caching, and execution lifecycles.
- SQL Injection Prevention: Why string concatenation is dangerous and how to use parameterized dynamic execution.
- Parameterized Dynamic SQL: Executing parameterized queries using
sp_executesql(SQL Server) andEXECUTE ... USING(PostgreSQL / PL/pgSQL). - Identifier Escaping: Safely handling dynamic table and column names with
quote_ident()andQUOTENAME(). - Query Plan Cache Implications: Avoiding plan cache pollution when generating ad-hoc dynamic queries.
The Danger of Raw String Concatenation: SQL Injection
The most critical danger of dynamic SQL is SQL Injection. Never concatenate untrusted user input directly into a dynamic SQL string:
-- DANGEROUS VULNERABLE CODE (DO NOT DO THIS!):
-- Suppose @user_input = "admin' OR '1'='1"
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM users WHERE username = ''' + @user_input + '''';
EXEC(@sql);
-- The query becomes: SELECT * FROM users WHERE username = 'admin' OR '1'='1';
-- The attacker bypasses authentication completely!
Safe Dynamic SQL: Parameterization
The correct way to execute dynamic SQL is through parameterized system procedures. Parameters are passed separately as strongly-typed values, completely neutralizing injection risks.
1. Microsoft SQL Server: sp_executesql
DECLARE @sql NVARCHAR(MAX);
DECLARE @param_definitions NVARCHAR(500);
DECLARE @input_department VARCHAR(50) = 'Engineering';
DECLARE @input_min_salary NUMERIC(10, 2) = 80000.0;
-- 1. Construct template with parameter placeholders (@dept, @min_sal)
SET @sql = N'SELECT employee_id, full_name, salary
FROM employees
WHERE department = @dept AND salary >= @min_sal;';
-- 2. Define parameter signature
SET @param_definitions = N'@dept VARCHAR(50), @min_sal NUMERIC(10, 2)';
-- 3. Execute safely with parameters
EXEC sp_executesql
@stmt = @sql,
@params = @param_definitions,
@dept = @input_department,
@min_sal = @input_min_salary;
2. PostgreSQL (PL/pgSQL): EXECUTE ... USING
In PostgreSQL stored procedures and anonymous code blocks (DO $$ ... $$), use EXECUTE with the USING clause:
DO $$
DECLARE
v_sql TEXT;
v_status TEXT := 'ACTIVE';
v_limit INT := 10;
v_rec RECORD;
BEGIN
-- Construct query with numbered parameter placeholders ($1, $2)
v_sql := 'SELECT user_id, email FROM users WHERE status = $1 ORDER BY user_id LIMIT $2';
-- Execute safely with bound parameters
FOR v_rec IN EXECUTE v_sql USING v_status, v_limit LOOP
RAISE NOTICE 'User: %, Email: %', v_rec.user_id, v_rec.email;
END LOOP;
END $$;
Dynamic Identifiers (Table Names & Column Names)
Parameters ($1 or @param) can only replace data values (literals). You cannot parameterize table names, column names, or SQL keywords:
-- INVALID: Parameters cannot be table names!
-- SELECT * FROM $1 WHERE id = $2; <-- FAILS!
When building dynamic queries that require dynamic table or column names, you must strictly sanitize and escape them:
- PostgreSQL: Use
quote_ident(table_name)or format specifier%Iinformat(). - SQL Server: Use
QUOTENAME(table_name).
-- Safe Dynamic Table Archival in PostgreSQL
CREATE OR REPLACE PROCEDURE archive_monthly_data(p_table_name TEXT, p_cutoff_date DATE)
LANGUAGE plpgsql AS $$
DECLARE
v_sql TEXT;
BEGIN
-- format() with %I safely escapes the identifier, preventing injection
v_sql := format('DELETE FROM %I WHERE created_at < $1', p_table_name);
EXECUTE v_sql USING p_cutoff_date;
RAISE NOTICE 'Successfully pruned table: %', p_table_name;
END;
$$;
Query Plan Caching & Performance Considerations
- Plan Reuse with Parameterization: When you use
sp_executesqlorEXECUTE ... USING, the database engine creates a single cached execution plan parameterized for those inputs. Subsequent executions reuse the compiled plan, saving parsing and optimization CPU time. - Plan Cache Thrashing with Raw Strings: Concatenating raw literals (
EXEC('SELECT ... WHERE id = ' + 10)) generates a brand new query string for every unique ID. This pollutes the database plan cache with millions of single-use plans, exhausting database RAM.
Common Mistakes & Gotchas
- Double Escaping Quotes: Manually escaping single quotes using
REPLACE(input, '''', '''''')is error-prone and misses edge cases. Always use proper parameterized APIs. - Overusing Dynamic SQL in Application Backends: If an application backend (Python, Java, Go) is executing dynamic queries, build the dynamic query in application code using an ORM or parameterized query builder (
psycopg2.sql, SQLAlchemy), rather than storing massive dynamic SQL strings inside the database.
Interview Perspective
SQL Interview Question: Why can't table names be parameterized in dynamic SQL, and how do you prevent SQL injection on table names?
Key Talking Points for Candidates:
- Compilation Lifecycle: The database query optimizer needs to resolve table names, schema definitions, and column statistics during the compile phase to build the execution plan before parameter bindings are evaluated.
- Safe Identifier Construction: To safely handle dynamic table or column names, developers must use engine identifier escapers:
QUOTENAME()in SQL Server orformat('%I', table_name)/quote_ident()in PostgreSQL, or validate the input against a strict whitelist of known table names ininformation_schema.tables. - Data Values: All user-supplied filter values must be passed via parameter lists (
sp_executesql/USING) rather than concatenated into the SQL string.
Interactive Practice Challenge
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.