Dynamic SQL, and when a static query is the smarter call

Dynamic SQL is SQL that your application assembles as a string while it runs, so the statement can change with variables, conditional logic, or whatever a user selects, instead of being fixed the moment you write it. That flexibility lets it handle inputs you can't predict ahead of time, and it also opens the door to security holes and unstable performance when you reach for it without care.

Most analytics work never needs it. You write a query for one region, finance asks for the same cut across twelve, and the filter on your dashboard can send any combination of those twelve back to the database. Static SQL, written once and run as-is, answers that with another rewrite, while dynamic SQL answers it with a single statement that adapts. Knowing which problem you actually have is most of the skill.

What is dynamic SQL?

Dynamic SQL is a query your code builds at runtime and then hands to the database to parse and execute. Static SQL is the opposite, a fixed statement the engine sees unchanged on every run, parses once, and caches a plan for.

The classic case for it is a report that has to run against a different client schema each night. Rather than maintain twelve near-identical queries, you keep one and pass the value in as a bound parameter.

-- The value is bound as a parameter, never pasted into the string
EXECUTE sp_executesql
  N'SELECT * FROM sales WHERE region = @region',
  N'@region nvarchar(50)',
  @region = @user_region;

You stop maintaining ten copies of one idea. The trouble starts the moment you build that string by pasting values into it instead of binding them.

Static SQL vs dynamic SQL, what's the difference?

The two return the same kind of results, but they behave differently in the places that matter most, from how the database plans them to how safe they are to ship.

Dynamic SQL examples

A few patterns show where dynamic SQL helps and where it hurts.

The pattern that causes SQL injection

Concatenating user input straight into a query is how SQL injection has worked for twenty-five years, and it still tops breach reports because it still ships to production. A value like '; DROP TABLE sales;-- stops being hypothetical the moment your code looks like the first line below.

-- Unsafe: user input becomes executable code
"SELECT * FROM sales WHERE region = '" + user_input + "'"

-- Safe: bind the value so the engine treats it as data
"SELECT * FROM sales WHERE region = @region"   -- then bind @region

The fix is not cleverness. Bind every value as a parameter, and validate input for type and shape before it goes anywhere near the engine.

Building a pivot when the columns aren't known yet

Some logic genuinely needs runtime assembly. If you pivot sales by month and the set of months isn't known until the query runs, you have to build the column list on the fly. You can't bind a column list as a parameter, so escape each identifier with QUOTENAME to keep it safe.

-- Columns aren't known until runtime, so build them safely
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(month_name), ',')
FROM (SELECT DISTINCT month_name FROM sales) AS m;

SET @sql = N'SELECT region, ' + @cols + N'
FROM sales
PIVOT (SUM(revenue) FOR month_name IN (' + @cols + N')) AS p;';

EXEC sp_executesql @sql;

Generating queries in application code

Most dynamic SQL is built in application code rather than inside the database, and the same rule holds there. Use the driver's placeholders for values and its identifier helpers for names, never string formatting. The psycopg2 example below selects a user-chosen set of columns without ever exposing an injection surface.

from psycopg2 import sql

# Identifiers via Identifier(), values via %s placeholders, never f-strings
query = sql.SQL("SELECT {cols} FROM sales WHERE region = %s").format(
    cols=sql.SQL(", ").join(map(sql.Identifier, selected_columns)),
)
cur.execute(query, [user_region])

When should you use dynamic SQL?

Use dynamic SQL when the shape of the query genuinely can't be known until runtime. For everything else, a static query with a bound parameter is the safer, simpler choice. Three situations earn the trade honestly.

• User-driven filters and search, where the number of possible queries is too large to hand-write and the statement has to follow whatever the user picked.
• Schema drift, where several teams touch the same tables and logic that reads column names at runtime survives a renamed field that would have broken hard-coded SQL.
• Runtime-shaped transformations like the pivot above, where the structure of the output depends on values you won't see until the query runs.

When to avoid dynamic SQL

Avoid dynamic SQL on routine reports where a static, parameterized query does the job. Hand-built query strings are harder to read and to test, and every value you concatenate is one more way in for an attacker.

The deeper question is where the variability should live. Building strings inside the database buries the most conditional logic in the hardest place to inspect. Push that logic up into an application or analytics layer that turns a user's choices into parameterized SQL for them, and the part that changes sits in software a person can actually read while the database still receives clean, bound statements.

Dynamic SQL best practices

A short checklist keeps dynamic SQL from becoming the fragile part of your stack.

• Bind values as parameters. Never concatenate user input into the statement.
• Validate input for type, range, and shape before the query is built.
• Escape identifiers you can't bind, such as table or column names, with QUOTENAME or your driver's identifier helper.
• Log the final statement before it executes so you can see exactly what the engine runs.
• Watch execution plans, since the same query text can plan very differently as inputs change.

Frequently asked questions

Is dynamic SQL a security risk?

It can be. Concatenating user input into a query string lets an attacker run their own SQL, which remains one of the most common breaches on the web. The risk goes away when you bind values as parameters and validate input for type and shape before the query runs.

Does dynamic SQL hurt performance?

Sometimes. Because the statement changes per run, the database often can't reuse a cached execution plan, and two near-identical queries can run at very different speeds. Static, parameterized queries stay predictable, so save dynamic SQL for cases where the query shape truly has to change.

When should I use dynamic SQL instead of a static query?

Reach for dynamic SQL when the query shape is unknown until runtime, like user-driven filters with countless combinations, schemas that shift across teams, or pivots whose columns depend on live data. When a static query with a bound parameter covers the case, that stays the faster and safer choice.