What is dynamic SQL, and what are its risks?
Quick Answer
Dynamic SQL is building a SQL statement as a string at run time and executing it. It's needed for genuinely dynamic structure — variable column lists, table names, or optional filters. The big risk is SQL injection if you concatenate user input; the mitigation is sp_executesql with parameters, not EXEC(@string).
Dynamic SQL is assembling a query as a string and running it, which you need when the shape of the query varies — a column list you don't know until run time, an optional set of filters, a table name that's a parameter. The danger is injection if any of that string comes from user input. The rule is to use sp_executesql with proper parameters rather than EXEC with string concatenation — that parameterises the values safely and also lets the plan be reused. Identifiers like table names can't be parameters, so I validate those against a whitelist or wrap them in QUOTENAME.
EXEC (@sql)— concatenation-based, no parameters, injectable, and each different string is a separate cached plan.EXEC sp_executesql @sql, N'@id int', @id = @id— parameterised: safe from injection for the values, and the plan is reused across calls.- Identifiers (table/column names) can't be parameters — validate them against a whitelist or use
QUOTENAME(). - Downsides: harder to read, debug and maintain; permissions (dynamic SQL runs in the caller's context unless the proc uses
EXECUTE AS); plan cache bloat if not parameterised.