skip to content

In SQL, what is dynamic SQL, and how do you tell when a static statement with bind parameters is already enough?

level: juniorimportance: should knowfreq 34%

answer

  1. text fixed or text assembled
  2. does the value change, or the shape?
  3. table, column, sort, predicate set
  4. a few known shapes: fixed statements

basics

~20 s

Dynamic SQL is statement text assembled at run time and then sent to the server. A static statement with bind parameters is enough whenever only values change, not the tables, columns, clauses or sort order.

solid answer

~50 s

**Dynamic SQL** means the program assembles the statement text at run time and only then sends it to the server to parse. A **static** statement has text fixed when the code is written; its values can still vary through bind parameters, as in `WHERE tenant_id = $1`, so a parameter alone does not make it dynamic. The test is whether the shape changes. If only values change, stay static. If the table, a column, the sort order or the set of predicates changes at run time, a parameter cannot express that, so you either choose among a few fixed statements or build the text. Static is the default because its text can be read in review, found by search and tested once. A built statement exists only at run time, so a typo in one branch surfaces only when that branch first runs.

code

sql · 6 lines
sql
PREPARE open_orders (int, text) AS
  SELECT id, total
  FROM orders
  WHERE tenant_id = $1 AND status = $2;

EXECUTE open_orders(42, 'open');

go deeper

for a junior

Recall the one-line test: if only values vary, use a static statement with bind parameters; if the table, column, sort or predicate set varies, the text has to change.

for a middle

Explain why shape cannot be a parameter in outline, then show the static escapes first: a few fixed statements, a CASE sort, or a view, before reaching for text assembly.

for a senior

Show you weigh the cost: late errors in rare branches, statements that search cannot find, and a test surface that grows with every independent choice the statement allows.

for a principal

Treat a demand for dynamic SQL as a question about the data model and the product surface, because a varying table is often a design choice that could become a column.

## Two kinds of statement A **static** SQL statement is one whose text is settled when the code is written. It may still contain **bind parameters**: placeholders such as `$1` or `?` whose values are supplied when the statement runs. The text is the same on every call, and only the values differ. **Dynamic SQL** is a statement whose text is assembled while the program runs. The program decides which words go into the statement, joins them into a string, and hands that string to the server, which parses it as it would any other statement. Nothing about the server's work makes it special; what differs is where the text came from. Beginners often blur this. A statement that takes a value from a user is not dynamic because of that. It only becomes dynamic when the input changes the text. ## The test: value or shape? Ask what actually varies from call to call. A value is data the statement compares, stores or returns. The **shape** is everything else: which tables it reads, which columns it names, which clauses and predicates it has, and how it sorts. | What varies at run time | Is it a value or the shape? | What you write | |---|---|---| | The customer id to match | Value | Static, `WHERE customer_id = $1` | | The end of a date range | Value | Static, `WHERE created_at < $2` | | How many rows to return | Value | Static in PostgreSQL and MySQL, `LIMIT $3` | | Which table to read | Shape | Dynamic, or a choice of fixed statements | | Which column to sort on | Shape | Dynamic, or a choice of fixed statements | | Which filters apply at all | Shape | Dynamic, or a catch-all predicate | | The column list, such as whichever month columns exist | Shape | Dynamic | The last row is why a query that unpivots a column list read from the catalog cannot stay static: column names are part of the text. ## Enough without building text Many requirements that sound dynamic have a small, closed set of shapes, and these stay static: - **A few known shapes.** A report with three sort orders can hold three complete statements, and the code picks one. Nothing is assembled. - **A sort chosen with `CASE`.** `ORDER BY CASE WHEN $1 = 'name' THEN name END` keeps one text. You need one `CASE` per column type, because all branches of a `CASE` share a type, and an index on the column usually cannot serve that sort. - **A view.** If the variety is really the same join with different columns, a view can hide the difference. - **One table with a discriminating column.** Reads that pick among similarly shaped tables often turn into a value once those tables become one table with a `tenant_id` or `region` column. ## What building the text costs Dynamic SQL is an ordinary tool, not a defect, but it is not free. 1. **Review.** A reader sees fragments and branches, not the statement, and has to imagine each result. 2. **Search.** Looking for every statement that touches a column can miss one assembled from pieces. 3. **Late errors.** A typo in a rarely taken branch stays hidden until that branch runs. 4. **Testing.** A static statement is tested once. A built one has as many shapes as its branches can combine into. 5. **Names in the text.** A table or column name has to be written into the string, and an unquoted one taken from input is injectable, so it needs handling that a value does not. Those costs are why static is the default and dynamic has to earn its place. ## A worked example Take a multi-tenant admin report. It always filters on the tenant, optionally on a status, and lets an administrator pick the table (live or archived orders) and the sort column. - The tenant id and the status are **values**, so bind parameters handle them. - The table and sort column are **shape**. With two tables and three sort columns, six fixed statements are possible, though tedious. Add more filters and the combinations outgrow fixed statements, which is the point where building the text is justified. A useful habit in an interview is to name the variable part first and only then the mechanism, because that shows you considered whether dynamic SQL was needed at all.

  • A report offers three sort orders, all known when the code is written. Does that require dynamic SQL?
    No. Keep three complete statements and let the code pick one, or use a `CASE` expression per sort column inside one statement. Text only has to be assembled when the combinations are too many to write out, which usually comes from several independent choices multiplying, not from one small choice.
  • Is a constant SQL string in application code dynamic SQL because the program sends it at run time?
    No. Every statement is sent at run time. What matters is whether input can change the text. A constant string with placeholders is static in every sense that matters: it can be read, searched for and tested as one statement. It turns dynamic once a branch or a variable adds words to it.

  • Any statement that takes a bind parameter counts as dynamic SQL.
  • Dynamic SQL is needed whenever a filter value comes from the user.
  • A bind parameter can choose the sort column just as it chooses a filter value.
  • Static SQL means the statement has to live in a stored procedure.
  • Dynamic SQL can never be made safe, so no real system should use it.