Explain like I'm five · Database

What is Dynamic SQL?

Dynamic SQL, told as a story about a warehouse worker assembling the order slip on the spot, adding whatever conditions each request actually needs.

just by zonecolor and zoneno filter, everythingevery request asks for a different combination of filters

Every request asks for a different combination of filters.One fixed form can't cover every combination.

zone onlycolor onlybothyou can't pre-print a form for every filter combination...so some pre-printed forms go unused

Fixed forms alone can't be pre-printed for every combination.More combinations just means more unused forms piling up.

zone B + redDynamic SQL = assembling the order slip on the spot, for whatever was askedadd the conditions the request has, skip the rest, then run the slip

Dynamic SQL assembles the order slip on the spot, for whatever was asked.Add the conditions the request has, skip the rest, then run the slip.

Build it at request timeassembled right before it runs
Add a piece if requestedan extra WHERE condition
Slot values in safelyparameter binding
Run once assembledexecutes the finished statement
base slip+zone=B+color=red→done!only add a condition piece when the request actually asks for it

Only add a condition piece when the request actually asks for it.Base slip + condition pieces = the finished SQL statement.

slot only the value into a blankpasting a customer's text rawpasting a customer's text in raw can let in an unwanted instruction (SQL injection)

Only values should go into the slip's blanks, slotted in safely.Pasting a customer's text in raw can let an unwanted instruction slip in (SQL injection).

In one breath

Dynamic SQL assembles a SQL statement on the spot, at run time, based on whatever conditions apply — the opposite of a fixed, pre-written static SQL.

Static SQL is fixed at compile (or stored-procedure registration) time, so its execution plan is easy to reuse. Dynamic SQL instead suits screens with variable, optional filters. But concatenating raw strings opens the door to SQL injection — values must always be slotted in safely through parameter binding.

When grown-ups say it

Static SQL
A pre-printed, fixed form. Easy to reuse the execution plan.
Dynamic SQL
A form assembled on the spot. Suits screens with variable filters.
Parameter Binding
Safely slotting just the value into a blank. Never concatenates raw text.
SQL Injection
An unwanted instruction slipping into the blank. Covered in depth in the security field. → the bad instruction that sneaks in
PREPARE/EXECUTE
Preparing the SQL form once, then rerunning it with new values. Faster when many requests share the same shape.
Dynamic SQL in Stored Procedures
Assembling a SQL statement inside a stored procedure. The assembled statement may be permission-checked apart from the procedure, so permission setup gets trickier.
Plan Cache Reuse
SQL of the same shape doesn't need a new execution plan. Dynamic SQL whose shape keeps changing makes this reuse harder.
ORM Query Builder
A tool that assembles the slip in code. Generates dynamic SQL under the hood.
Next story
Splitting and Doubling Up the Shelves →
Want another term as a picture book? Request →