Dynamic SQL in SQL Server: sp_executesql, SQL Injection, and What Changed in SQL Server 2025
Dynamic SQL, building a query as a string and executing it at run time, is one of the most useful and most misused tools in SQL Server. Used well, it automates DBA tasks that would otherwise require dozens of hand-written scripts, and it lets application code handle search screens with a dozen optional filters without a dozen near-identical stored procedures. Used carelessly, it opens SQL injection holes, floods the plan cache with one-off query plans, and produces code nobody on the team can safely modify six months later.
This article covers the mechanics that actually matter: the difference between sp_executesql and EXEC(), why parameterization is not optional, how QUOTENAME protects object names, a Unicode conversion trap that defeats that protection silently, and the plan-cache cost of getting any of this wrong. It closes with what changed in SQL Server 2025, since that part didn’t exist the last time most of the SQL Server world’s reference material on this topic was written.
- What Dynamic SQL Actually Is
- sp_executesql vs EXEC(): The Real Difference
- The Core Rule: Parameters, Not Concatenation
- Protecting Object Names with QUOTENAME
- The Unicode Trap That Defeats QUOTENAME Silently
- Plan Cache Bloat: Why Inlined Values Hurt Everyone
- SQL Server 2025: OPTIMIZED_SP_EXECUTESQL
- The One Debug Habit Worth Keeping
- When Dynamic SQL Is the Wrong Tool
- Quick Reference Checklist
- Further Reading
- References
1What Dynamic SQL Actually Is
Dynamic SQL is T-SQL code assembled into a string variable at run time and then executed, as opposed to static SQL written directly into a stored procedure or script and compiled once. Anything legal in a stored procedure is legal inside a batch of dynamic SQL; the difference is purely that the code exists as text until the moment it runs. A batch of dynamic SQL is, functionally, a nameless stored procedure: it gets its own execution scope, its own variable visibility, and its own entry in the plan cache.
That scope boundary matters immediately. Variables declared in the calling procedure are not visible inside the dynamic SQL, and variables declared inside the dynamic SQL disappear when it finishes. Anything the dynamic batch needs from the caller has to be passed in explicitly as a parameter.
2sp_executesql vs EXEC(): The Real Difference
SQL Server offers two ways to run a string as T-SQL: the EXEC() statement and the sp_executesql system procedure. They are not interchangeable in practice.
| sp_executesql | EXEC() | |
|---|---|---|
| Accepts parameters | Yes | No, values must be inlined |
| String data type required | nvarchar only | varchar or nvarchar |
| Plan reuse across calls | Reliable, since parameter values stay out of the string text | Poor, since every distinct value produces a distinct string |
| OUTPUT parameters | Supported | Not supported |
Per Microsoft’s own documentation, sp_executesql supports substituting parameter values for the string it runs, while EXEC() does not; because the actual text of the statement passed to sp_executesql doesn’t change between calls, the query optimizer can match it against a previously cached execution plan and skip recompilation. With EXEC(), every parameter value has to be converted to a string and concatenated in, so a genuinely new string is built on every call even when only a value changed. That single distinction is the reason sp_executesql should be the default choice, and EXEC() reserved for the narrow case of running a fixed string with no parameters at all.
3The Core Rule: Parameters, Not Concatenation
Anything that can be a parameter should be one. Here is the pattern:
DECLARE @sql nvarchar(MAX), @params nvarchar(200); SET @sql = N'SELECT CompanyName FROM dbo.Customers WHERE CustomerID = @custid'; SET @params = N'@custid nchar(5)'; EXEC sp_executesql @sql, @params, @custid = N'ALFKI';
@stmt and @params must both be nvarchar; sp_executesql rejects varchar for those two arguments specifically. Use nvarchar(MAX) for the statement itself, not a fixed length: a shorter type risks silent truncation if the generated batch grows past the declared length, which produces confusing syntax errors that have nothing to do with the actual logic.
4Protecting Object Names with QUOTENAME
Parameters solve the problem for values. They do not solve it for object names: a table name, column name, or schema name can never be passed as a parameter, since SQL Server needs to resolve identifiers at parse time, not substitute them at execution time. When an object name has to be concatenated into a dynamic SQL string, QUOTENAME is the built-in defense.
SELECT QUOTENAME('Order Details');
-- [Order Details]
SELECT QUOTENAME('abc]def');
-- [abc]]def]
QUOTENAME wraps its input in delimiters (brackets by default) and doubles any closing delimiter that already appears inside the string, which is what prevents a maliciously or accidentally crafted name from breaking out of the identifier and altering the statement. Per Microsoft’s documentation, the input is typed as sysname, meaning it accepts at most 128 characters; anything longer returns NULL rather than a truncated or partially escaped result. That 128-character ceiling is fine for object names, since SQL Server identifiers are already capped there, but it means QUOTENAME is the wrong tool for escaping a string literal that could reasonably exceed that length, such as a file path.
5The Unicode Trap That Defeats QUOTENAME Silently
There is a subtler failure mode worth knowing about, and it has nothing to do with using QUOTENAME incorrectly. It comes from declaring the wrong data type for the variable that holds the finished SQL string.
DECLARE @sql varchar(MAX); -- wrong type for this purpose
When an nvarchar value is converted to varchar, any character that doesn’t exist in the code page used by the current collation is replaced with a fallback character chosen by SQL Server, not rejected. Object names pulled from system views such as sys.tables are nvarchar, so if the dynamic SQL string that eventually holds them is declared as varchar, characters outside that collation’s code page can silently collapse into something else, including, in specific cases, characters that carry syntactic meaning once the fallback substitution happens. QUOTENAME did its job correctly on the original nvarchar value; the damage happens afterward, during a conversion most people never think to check. The fix is simple and absolute: the variable holding dynamic SQL should always be nvarchar, never varchar, for exactly this reason as well as the type requirement sp_executesql already enforces.
6Plan Cache Bloat: Why Inlined Values Hurt Everyone
SQL Server looks up a stored procedure’s cached plan by the procedure’s name. A batch of dynamic SQL has no name, so SQL Server instead computes a hash of the exact query text, including the parameter list passed to sp_executesql, and looks up the cache entry by that hash. The hash is computed on the literal text: no normalization of whitespace, case, or comments, and critically, no awareness of what the values inside the string mean.
If parameter values are concatenated directly into the string instead of passed through sp_executesql‘s parameter list, every distinct value produces a distinct string and therefore a distinct cache entry. A search procedure called with a thousand different customer IDs over a concatenation pattern produces up to a thousand separate plans sitting in the cache, each consuming memory, each a candidate for a hash collision, and each representing compilation work that a parameterized version would have paid only once. This is the same reason Microsoft’s own documentation on sp_executesql recommends it specifically for statements executed repeatedly with only the parameter values changing: the query optimizer can match the unchanged statement text against a previously generated plan and skip recompilation entirely.
Schema-qualify every object referenced in dynamic SQL, even when every table lives in dbo. An unqualified table name means SQL Server has to account for the possibility that a user’s default schema resolves it differently, which can prevent two sessions from sharing the same cached plan even when the query text is otherwise identical.
7SQL Server 2025: OPTIMIZED_SP_EXECUTESQL
Parameterization solves cache bloat from distinct values, but it doesn’t solve a related problem: a compilation storm, where a large number of sessions call the same, already-parameterized sp_executesql batch simultaneously and each one compiles its own plan before the first compiled plan is available to reuse. SQL Server 2025 introduces a database-scoped configuration, OPTIMIZED_SP_EXECUTESQL, specifically for this case.
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_SP_EXECUTESQL = ON;
According to Microsoft’s documentation, enabling this setting makes the compilation behavior of sp_executesql batches match the serialized compilation behavior that stored procedures and triggers already use. When multiple sessions call an identical batch at the same time, only the first session compiles the plan; the others wait on a compile lock and then reuse the plan the first session produced, rather than each independently compiling its own. This is purely a compilation-concurrency improvement, not a change to parameterization or plan-cache lookup itself, and Microsoft’s guidance is to enable it selectively based on observed workload rather than turning it on everywhere by default. It’s available starting with SQL Server 2025, and also documented for Azure SQL Database and SQL database in Microsoft Fabric.
8The One Debug Habit Worth Keeping
The most common reason dynamic SQL is hard to troubleshoot is that nobody can see what string actually got built. A @debug parameter that prints the finished SQL before executing it turns an opaque failure into a readable one:
CREATE OR ALTER PROCEDURE dbo.example_sp
@debug bit = 0
AS
...
IF @debug = 1 PRINT @sql;
EXEC sp_executesql @sql, @params, ...;
For strings longer than 4000 characters, PRINT truncates the output, so a very long generated batch may need to be broken into chunks or returned via SELECT instead. Either way, the habit itself, always being able to see the generated text on demand, is what turns dynamic SQL debugging from guesswork into a normal troubleshooting process.
9When Dynamic SQL Is the Wrong Tool
Dynamic SQL earns its complexity when the thing that varies is genuinely structural: which table, which columns, how many search conditions, how many pivoted columns. It’s the wrong tool when it’s being used to work around a design that should have handled the variation relationally in the first place. A few recurring patterns:
- A table name passed as an application parameter, when the real answer is that the design needs one well-defined table, not a family of similarly-named ones selected at run time.
- A full
WHEREclause passed in from client code as a string, which reintroduces every SQL injection risk parameterization exists to prevent, and couples the client tightly to the server’s schema. - A comma-separated list concatenated directly into an
INclause, which is both an injection risk and, separately, doesn’t do what most people assume:col IN ('1,2,3')looks for the literal string1,2,3, not three separate values.
In each case, the fix isn’t cleverer dynamic SQL; it’s a different design; a list-splitting function, a table-valued parameter, or client-side query construction, depending on the case.
10Quick Reference Checklist
- Default to
sp_executesql; reserveEXEC()for fixed strings with no parameters. - Every value that can be a parameter must be one. Never concatenate a value into the SQL string.
- Wrap every object name pulled from a variable in
QUOTENAMEbefore concatenating it in. - Declare the SQL string variable as
nvarchar(MAX), nevervarchar, to avoid both truncation and the Unicode fallback trap. - Schema-qualify every object in the generated SQL, even in single-schema databases.
- On SQL Server 2025, evaluate
OPTIMIZED_SP_EXECUTESQLif the workload shows signs of a compilation storm. - Always include a
@debugparameter that prints the generated SQL.
11Further Reading
The single most thorough resource on this topic in the SQL Server community is Erland Sommarskog’s article The Curse and Blessings of Dynamic SQL. Sommarskog is a long-time SQL Server MVP, and his article goes far deeper than this one into style-guide-level practices, advanced nested-string techniques, dynamic pivot construction, and a long list of production war stories. Anyone who wants the exhaustive version after reading this overview should go there directly.
References
- Microsoft Docs: sp_executesql (Transact-SQL) (Microsoft Learn)
- Microsoft Docs: QUOTENAME (Transact-SQL) (Microsoft Learn)
- Microsoft Docs: Intelligent Query Processing, OPTIMIZED_SP_EXECUTESQL (Microsoft Learn)
- Microsoft Community Hub: The Double-Edged Sword of Dynamic SQL: Anti-Patterns, Threats, and Solutions (Microsoft Community Hub, 2025)
- Further reading: Erland Sommarskog, The Curse and Blessings of Dynamic SQL (sommarskog.se)
- SQLYARD: SQL Server Security Best Practices 2026: Least Privilege, Roles, and Authentication Guide
- SQLYARD: SQL Server 2025 Fundamentals: A Practical Guide from Basics to Advanced
All technical content on SQLYARD is verified against Microsoft documentation at the time of publication. SQL Server features, cloud service capabilities, licensing terms, and configuration requirements can change between versions and cumulative updates. Always validate against current Microsoft Learn documentation before deploying to production.
Discover more from SQLYARD
Subscribe to get the latest posts sent to your email.


