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.

How Sigma handles dynamic SQL

Sigma is one place this idea shows up directly. You write custom SQL inside a workbook and reference its controls right in the statement, so a user's dropdown, slider, or date picker shapes the query while you stay out of the business of gluing strings together.

-- In a Sigma workbook, a control drives the filter, no concatenation
SELECT *
FROM sales
WHERE region = {{region-control}}

When the thing that changes is a name rather than a value, which is the case that usually forces string-building, Sigma has a directive for it. The {{#identifier [control]}} token passes a column or table name from a control safely, and sigma_element('Sales by region') lets one SQL element build on another without copying logic.

Because the query runs inside a governed workbook, the guardrails travel with it. Row-level security and user attributes scope the same statement to whatever each person is allowed to see, and a connection can even choose its warehouse role from those attributes, so a dynamic query stays a governed one rather than a hole someone has to remember to patch.

The same controls reach past the workbook. In an embedded app you can set their values straight from the embed URL, including JWT-signed embeds, so the host application drives the dynamic SQL and each customer sees only their slice without anyone writing a separate query per tenant.

Controls aren't limited to single values, either. A range control hands its bounds to the query through tokens like {{#formula [date-range].start}}, and a multi-select passes an array straight into an IN list.

-- Date-range and multi-select controls feed the query directly
SELECT *
FROM orders
WHERE order_date BETWEEN {{#formula [date-range].start}} AND {{#formula [date-range].end}}
  AND region IN ({{region-multi}})

Two limits are worth knowing up front. Custom SQL that references another element with sigma_element() can't be materialized, and column-level security doesn't automatically follow a column pulled in that way, so you reapply those protections where they matter.

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.

How does Sigma handle dynamic SQL?

In Sigma, custom SQL lives in a workbook and reads its controls directly through tokens like {{control-id}}, so a user's selections shape the query without string concatenation. The {{#identifier}} directive passes column or table names safely, and row-level security plus user attributes keep results scoped per user.