Dynamic SQL is widely used in enterprise systems. It powers advanced filtering, flexible reporting, role-based visibility, and multi-tenant logic. But when developers concatenate strings and build SQL directly with values, the result is unsafe, slow, and unpredictable.
Poorly written dynamic SQL creates SQL injection risks, high CPU usage, excessive recompiles, and plan-cache pollution. The solution is to refactor dynamic SQL using proper parameterization.
This article explains the full process end-to-end with practical patterns, code samples, performance insights, and diagrams that demonstrate how SQL Server internally behaves.
Why Dynamic SQL Becomes A Problem
Dynamic SQL is not the issue. The problem is how developers construct it.
A Common Unsafe Pattern
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM Sales WHERE Region = ''' + @region + '''';
EXEC(@sql);
Problems created:
SQL injection exposure
Every query string is unique, so SQL Server creates a new execution plan
High CPU usage due to plan-cache pollution
Bad cardinality estimates
Unpredictable performance
ASCII Diagram: What Happens Inside SQL Server
+--------------+ +------------------------+
| Incoming SQL | ----> | SQL Text Normalization |
+--------------+ +------------------------+
|
v
+-----------------------------+
| Plan Cache Lookup Fails |
| (because literals differ) |
+-----------------------------+
|
v
+--------------------+
| Compile New Plan |
+--------------------+
|
v
+----------------------------+
| Execute With Wrong Estimates|
+----------------------------+
Every incoming literal generates a different plan, causing unnecessary compilation and degraded performance.
Identifying Hotspots Before Refactoring
Before refactoring, identify areas where dynamic SQL is hurting production performance.
Check Plan Cache For Literal-Based Queries
SELECT TOP 20
qs.execution_count,
qs.total_worker_time,
st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;
Look for:
Find Unsafe Concatenation
Search in your codebase for:
Detect Plan Cache Bloat
SELECT
objtype,
COUNT(*) AS totalPlans
FROM sys.dm_exec_cached_plans
GROUP BY objtype;
Too many adhoc plans indicate dynamic SQL without parameters.
Migrate To sp_executesql Properly
The most important step is replacing EXEC() with sp_executesql.
Unsafe Dynamic SQL
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM Orders WHERE CustomerId = ' + CAST(@customerId AS NVARCHAR(10));
EXEC(@sql);
Parameterized Dynamic SQL (Safe And Fast)
DECLARE @sql NVARCHAR(MAX);
SET @sql = '
SELECT *
FROM Orders
WHERE CustomerId = @cid
';
EXEC sp_executesql
@sql,
N'@cid INT',
@cid = @customerId;
Advantages
Building Dynamic WHERE Clause Cleanly
Enterprise applications often require optional filters.
Dynamic WHERE Clause Pattern
DECLARE
@region NVARCHAR(50) = NULL,
@status NVARCHAR(20) = 'Active';
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM Customers WHERE 1=1';
DECLARE @params NVARCHAR(MAX) = N'';
IF @region IS NOT NULL
BEGIN
SET @sql += N' AND Region = @pRegion';
SET @params += N'@pRegion NVARCHAR(50),';
END
IF @status IS NOT NULL
BEGIN
SET @sql += N' AND Status = @pStatus';
SET @params += N'@pStatus NVARCHAR(20),';
END
SET @params = LEFT(@params, LEN(@params)-1);
EXEC sp_executesql @sql, @params,
@pRegion = @region,
@pStatus = @status;
Benefits
Clean code
Safe execution
Maximum plan reuse
Using Table-Driven Parameter Sets
For large filtering scenarios, manage parameters using a table.
DECLARE @Filter TABLE(
FilterName NVARCHAR(100),
SqlCondition NVARCHAR(200),
ParamName NVARCHAR(50),
ParamType NVARCHAR(50),
ParamValue SQL_VARIANT
);
Populate the table and build SQL dynamically. Useful in reporting and BI systems.
Applying Forced Parameterization When Possible
SQL Server supports automatic parameterization.
Database-Level Forced Parameterization
ALTER DATABASE MyDB SET PARAMETERIZATION FORCED;
When Not To Use It
Complex analytical queries
Multi-tenant row-level security queries
Queries using OPTION (RECOMPILE)
Manual parameterization gives more control.
Designing Reusable SQL Templates For Maintainability
Avoid building SQL directly in application code.
Example template store:
SELECT TemplateText
FROM SqlTemplates
WHERE TemplateName = 'CustomerSearch';
Benefits:
Diagram: How Parameterized Dynamic SQL Works
+--------------+
| SQL Template |
+--------------+
|
v
+-------------------+
| Parameter Binding |
+-------------------+
|
v
+---------------------------+
| Plan Cache Lookup (Works) |
+---------------------------+
|
v
+------------------+
| Execute Efficient |
+------------------+
SQL Server now sees identical query shapes, enabling reuse.
Case Study 1: Refactoring A Report Query
Original
SET @sql = '
SELECT *
FROM Sales
WHERE 1 = 1 ';
IF(@fromDate IS NOT NULL)
SET @sql += ' AND SaleDate >= ''' + CONVERT(NVARCHAR(30), @fromDate, 120) + '''';
IF(@toDate IS NOT NULL)
SET @sql += ' AND SaleDate <= ''' + CONVERT(NVARCHAR(30), @toDate, 120) + '''';
EXEC(@sql);
Refactored
DECLARE @sql NVARCHAR(MAX) = N'
SELECT *
FROM Sales
WHERE 1=1
';
DECLARE @params NVARCHAR(MAX) = N'';
IF @fromDate IS NOT NULL
BEGIN
SET @sql += ' AND SaleDate >= @from';
SET @params += '@from DATETIME,';
END
IF @toDate IS NOT NULL
BEGIN
SET @sql += ' AND SaleDate <= @to';
SET @params += '@to DATETIME,';
END
SET @params = LEFT(@params, LEN(@params)-1);
EXEC sp_executesql @sql, @params,
@from = @fromDate,
@to = @toDate;
Performance results
Case Study 2: Parameterizing A Multi-Tenant ACL Query
Unsafe
SET @sql = 'SELECT * FROM Orders WHERE TenantId = ' + CAST(@tenantId AS NVARCHAR(10));
Safe
SET @sql = 'SELECT * FROM Orders WHERE TenantId = @tid';
EXEC sp_executesql
@sql,
N'@tid INT',
@tid = @tenantId;
Multi-tenant systems benefit significantly because plans are reused correctly.
Advanced Pattern: Template With Placeholders
Use placeholders for table/column names, never for values.
Template
SELECT *
FROM {{TableName}}
WHERE Status = @status
Replacement
SET @sql = REPLACE(@template, '{{TableName}}', QUOTENAME(@tableName));
Then apply parameters normally.
Handling Sort Order Dynamically
Unsafe
ORDER BY ' + @sortColumn + ' ' + @sortDirection
Safe pattern
IF @sortColumn NOT IN ('Name','CreatedDate','Amount')
SET @sortColumn = 'Name';
IF @sortDirection NOT IN ('ASC','DESC')
SET @sortDirection = 'ASC';
SET @sql += ' ORDER BY ' + QUOTENAME(@sortColumn) + ' ' + @sortDirection;
Advanced Pattern: Using OPTION (RECOMPILE)
Use OPTION (RECOMPILE) only when:
Filters are highly skewed
Parameter sniffing causes unstable execution times
Running rare admin queries
Do not apply for high-throughput OLTP queries.
Diagram: Plan Cache Pollution Before And After Refactor
Before
Plan Cache:
sales_region_north_2023
sales_region_east_2023
sales_region_south_2024
sales_region_west_2022
...
(hundreds of variations)
After
Plan Cache:
sales_region_parameterized_plan (reused thousands of times)
Applying Parameterization Inside Stored Procedures
Correct pattern
EXEC sp_executesql
@sql,
N'@p1 INT, @p2 NVARCHAR(50)',
@p1 = @value1,
@p2 = @value2;
Always bind parameters explicitly.
Performance Testing Before And After Refactor
Metrics To Track
How To Measure
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
Common Mistakes During Refactoring
Concatenating values instead of parameters
Using NVARCHAR(MAX) unnecessarily
Forgetting to remove trailing commas
Not validating sort or column inputs
Overusing OPTION (RECOMPILE)
Mixing literal concatenation and parameters
Final Checklist For Safe Dynamic SQL
Always use sp_executesql
Validate all identifiers
Parameterize all filter inputs
Validate sorting inputs
Use QUOTENAME for table/column names
Avoid OPTION (RECOMPILE) unless required
Verify plan reuse after deployment
Conclusion
Dynamic SQL is essential in modern enterprise systems, but unparameterized SQL causes instability, inconsistent performance, and security risks. Using sp_executesql, safe dynamic filters, SQL templates, input validation, and proper plan reuse ensures stable performance and predictable behavior.