SQL code that is constructed as a string at runtime and executed using EXEC or sp_executesql, allowing queries to be built dynamically based on variable inputs or conditions.
- Dynamic SQL is used when query structure cannot be fully determined at development time: Examples include table names, column names, and filter conditions that vary based on user input or application logic.
- Parameterized dynamic SQL via
sp_executesqlis strongly preferred over string concatenation: It supports plan reuse, prevents SQL Injection, and improves security. - Common legitimate use cases include: Dynamic pivot queries, generic search procedures, administrative scripts that iterate across databases, and multi-tenant applications.
- Security risk: Dynamic SQL built through unvalidated string concatenation is the primary attack vector for SQL injection, a critical database security vulnerability.
- Performance consideration: Dynamic SQL can cause plan cache bloat if parameters are embedded directly in the string rather than parameterized. Use
sp_executesqlwith parameters whenever possible. - DBAs auditing for security threats should monitor: The execution of ad hoc and dynamic SQL statements using Extended Events or SQL Compliance Manager.
- Related terms: SQL Injection,
sp_executesql, Parameterized Query, Execution Plan, SQL Compliance Manager.
