Dynamic SQL

Status: Reviewed (deep-dive v1)
Stack note: Warehouse usp_* procs (SQL-12) sometimes need flexible filters — do it with sp_executesql + parameters, never string-glued user input. Blazor/PS still call procs; dynamic SQL stays inside SQL.
Pairs with: SQL-12 (procs), SQL-13 (TVP / #temp instead of CSV), SQL-05 (security).
Goal: When dynamic SQL is justified, how to parameterize it, and how not to get owned.

Quick chooser

Need	Prefer	Avoid
Optional filters on a board proc	Static SQL with `(@p IS NULL OR col = @p)`	Building `WHERE` from UI strings
Truly optional columns/order / search grids	`sp_executesql` + allowlisted identifiers	Concatenating raw column names from client
Multi-id list	TVP (SQL-13)	`IN (' + @csv + ')`
Run same text many times	`sp_executesql` (plan reuse)	`EXEC(@sql)` with literals baked in
Admin one-off in SSMS	Whatever’s clear	Shipping that pattern to Blazor

Default: If static SQL works, don’t go dynamic.

The two EXEC styles

Bad / last resort — `EXEC (@sql)`

DECLARE @sql nvarchar(max) =
  N'SELECT * FROM dbo.Alert WHERE Title = ''' + @Title + N'''';
EXEC (@sql);  -- injection + poor plan reuse

Good — `sp_executesql` + parameters

DECLARE @sql nvarchar(max) =
  N'SELECT AlertId, Title, Status
    FROM dbo.Alert
    WHERE (@Status IS NULL OR Status = @Status)
      AND (@Title IS NULL OR Title LIKE @Title)';

EXEC sys.sp_executesql
  @sql,
  N'@Status varchar(16), @Title nvarchar(256)',
  @Status = @Status,
  @Title = @Title;  -- pass N'%foo%' from caller if needed

Why better: Values are parameters (type-safe, escaped by engine); plans can reuse; injection surface shrinks to identifiers you still must allowlist.

Injection hygiene checklist

[ ] Never concatenate end-user text into SQL as literals
[ ] Parameters for all values (@Status, dates, ids)
[ ] Identifiers (table/column/ORDER BY): allowlist only — map UI tokens → fixed SQL fragments
[ ] Quote identifiers with QUOTENAME(@name) only after allowlist pass
[ ] Reject anything not in the map (fail closed)
[ ] No EXECUTE AS elevation unless designed and audited
[ ] App pool: EXECUTE on proc only — not db_owner
[ ] Code review: grep for EXEC ( and + @ in prod procs

Allowlist pattern (ORDER BY example)

DECLARE @orderBy nvarchar(200) =
  CASE @Sort
    WHEN 'severity' THEN N'sev.SortOrder ASC, a.FirstSeenAt ASC'
    WHEN 'age'      THEN N'a.FirstSeenAt ASC'
    ELSE N'a.AlertId ASC'  -- default; never pass @Sort through raw
  END;

SET @sql = N'SELECT … FROM … ORDER BY ' + @orderBy;
EXEC sys.sp_executesql @sql;  -- no user values in @orderBy

Optional filters without dynamic SQL

Often enough for SPIKE boards:

WHERE (@SqlInstanceId IS NULL OR i.SqlInstanceId = @SqlInstanceId)
  AND (@UnackedOnly = 0 OR a.Status = N'Open')

Watch: can inhibit index use on some shapes — measure (SQL-16 / Query Store). Still prefer this over string builds until proven painful.

Plan reuse & sniffing

sp_executesql → parameterized → better cache reuse than unique ad-hoc strings
Sniffing still applies — skewed estates may need OPTION (RECOMPILE) on the inner batch when measured
Don’t defeat reuse by baking distinct literals per call

Permissions & ownership

Dynamic SQL runs in the caller’s context unless EXECUTE AS — broken ownership chaining is common
Prefer static DML in the outer proc; dynamic only for the SELECT shape you must vary
Certificate signing / module signing if you must elevate carefully (rare for boards)

Estate patterns

Scenario	Pattern
Alert/inventory board filters	Static NULL-able params first
“Pick columns” export	Allowlisted column list → `sp_executesql`
PS passes search text	Proc param → `LIKE @p` inside `sp_executesql` or static
IN-list from Blazor	TVP join — not dynamic `IN (...)`
Collector pivot weirdness	Prefer staging `#temp` + static SQL (SQL-13)

Common failure patterns

Symptom	Likely cause
Odd `'` errors / odd rows	Quote concatenation / injection attempt
Plan cache flood	Unique `@sql` strings with literals
Works as you, fails as app	Ownership chaining + dynamic SQL
ORDER BY ignored / error	Client sent raw column; no allowlist
Security finding on proc	`EXEC (@sql)` with user input

Tiny lab

Proc with Title filter via concat vs sp_executesql — try x'; SELECT @@VERSION;--
Replace CSV IN with TVP
Add allowlisted sort — confirm unknown @Sort hits default

Tie-ins

SQL-12: Dynamic SQL lives inside usp_*, not in Blazor
SQL-13: TVP / #temp beat string lists
SQL-05 / SPIKE-06: Review dynamic procs in security passes

Done when

[ ] Team rule: values → parameters; identifiers → allowlist + QUOTENAME
[ ] No EXEC (@sql) with user input in warehouse procs
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-14 Dynamic SQL v1