Refactoring Dynamic SQL With Parameterization

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:

Same query shape with different literal values
Excessive compilations

Find Unsafe Concatenation

Search in your codebase for:

EXEC(
Repeated string concatenation
Literal values injected into SQL

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

Plan reuse improves
CPU cost reduces
SQL injection removed
Better cardinality estimates

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:

Clean code separation
No code redeploy for SQL changes
Consistent patterns across teams

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

78 percent fewer compilations
40 percent CPU reduction

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

CPU time
Logical reads
Compilations/sec
Plan cache entries
Query duration (P95, P99)
TempDB usage

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.