Dynamic SQL

Learn how to build and execute SQL strings at runtime with sp_executesql, and why parameterization matters for guarding against SQL injection.

Introduction

Most T-SQL is static: the shape of the query is fixed when you write it, even if the values plugged into it change. Sometimes, though, the structure of the query itself needs to change at runtime - which columns to sort by, which table to query, or how many optional filters to apply. Dynamic SQL is how you build and execute a SQL statement as a string at runtime. It is powerful, but it also opens the door to SQL injection if you are not careful, which this lesson covers in detail.

What You Will Learn

What dynamic SQL is and when it is genuinely needed.
How to build and run a SQL string with EXEC.
Why string-concatenated dynamic SQL is a SQL injection risk.
How sp_executesql safely parameterizes dynamic SQL.
How to build a realistic, safe dynamic search procedure.

What Is Dynamic SQL?

Dynamic SQL means constructing a T-SQL statement as a string, then asking SQL Server to compile and run that string. It is typically used when the structure of a query needs to vary - for example, a search screen where the user can optionally filter by name, city, and status, and you do not want to write a separate static query for every combination.

Building a SQL String with EXEC

The simplest way to run a dynamic SQL string is EXEC() with the string in parentheses.

DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT CustomerID, FirstName, LastName FROM dbo.Customers WHERE City = ''Seattle''';

EXEC (@sql);

Result

Click Run to see what this code prints.

The SQL Injection Risk

The example above hardcodes 'Seattle,' but in practice that value usually comes from user input. If you build the string by concatenating that input directly, a malicious value can change the meaning of the query entirely.

-- DANGEROUS: never do this with untrusted input
DECLARE @City VARCHAR(50) = ''' OR 1=1 --';
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT CustomerID, FirstName, LastName FROM dbo.Customers WHERE City = ''' + @City + '''';

EXEC (@sql);
-- The resulting string becomes:
-- SELECT ... WHERE City = '' OR 1=1 --'
-- which returns every row in the table, not just one city.

This Is SQL Injection

Concatenating user-supplied text directly into a SQL string lets an attacker change the query's logic - bypassing WHERE filters, reading unrelated data, or worse. This is one of the most serious and most common vulnerabilities in database-backed applications.

sp_executesql and Parameters

sp_executesql runs a dynamic SQL string with real, typed parameters - the same way a parameterized query works from application code. Because the value is passed as a parameter rather than concatenated into the string, it can never change the structure of the query, which eliminates the injection risk.

DECLARE @sql NVARCHAR(MAX);
DECLARE @City VARCHAR(50) = 'Seattle';

SET @sql = N'SELECT CustomerID, FirstName, LastName FROM dbo.Customers WHERE City = @CityParam';

EXEC sp_executesql
    @sql,
    N'@CityParam VARCHAR(50)',
    @CityParam = @City;

Result

Click Run to see what this code prints.

A Realistic Search Procedure

Here is a more realistic example: a search procedure with several optional filters, safely built with sp_executesql.

CREATE PROCEDURE dbo.SearchCustomers
    @City   VARCHAR(50) = NULL,
    @Status VARCHAR(20) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX) = N'
        SELECT CustomerID, FirstName, LastName, City, IsActive
        FROM dbo.Customers
        WHERE 1 = 1';

    IF @City IS NOT NULL
        SET @sql += N' AND City = @CityParam';

    IF @Status IS NOT NULL
        SET @sql += N' AND IsActive = @StatusParam';

    EXEC sp_executesql
        @sql,
        N'@CityParam VARCHAR(50), @StatusParam VARCHAR(20)',
        @CityParam = @City,
        @StatusParam = @Status;
END;
GO

EXEC dbo.SearchCustomers @City = 'Seattle';

When Dynamic SQL Is Appropriate

Good Fit	Better Handled Statically
Optional search filters (as above)	A fixed, known query shape
Dynamic column or table names for admin/reporting tools	Filtering by a value, which a normal parameter already handles
Building PIVOT queries where columns aren't known until runtime	Anything that can be expressed with plain parameters

Common Mistakes

Avoid These Mistakes

Concatenating user input directly into a SQL string instead of using sp_executesql parameters.
Using dynamic SQL when a plain parameterized query would already do the job.
Forgetting that table and column names cannot be parameterized with sp_executesql - only values can - so those must be carefully validated (e.g., against an allow-list) if they come from user input.
Not using QUOTENAME() when a dynamic identifier (like a table name) truly must be built into the string.
Losing readability by building deeply nested or very long dynamic SQL strings without comments.

Best Practices

Default to static, parameterized SQL; reach for dynamic SQL only when the query structure genuinely must vary.
Always pass values through sp_executesql parameters, never string concatenation.
Validate any dynamic identifier (table/column name) against a known allow-list, and wrap it with QUOTENAME().
Keep dynamic SQL construction in one well-tested procedure rather than scattering it across the codebase.
Log the generated SQL string during development to verify it builds correctly.

Frequently Asked Questions

Is dynamic SQL always dangerous?

No - dynamic SQL itself is a normal, supported feature. The danger comes specifically from concatenating untrusted input directly into the string instead of passing it as a parameter through sp_executesql.

Can sp_executesql parameterize table or column names?

No, only values (like a WHERE filter) can be parameterized. Table and column names must be built into the string itself, so they need extra validation, such as checking against a known allow-list and wrapping with QUOTENAME().

Does dynamic SQL hurt performance?

It can prevent plan reuse if the generated string varies slightly each time (extra whitespace, different literal values embedded directly). Using sp_executesql with real parameters helps SQL Server reuse cached execution plans, much like a parameterized query from application code.

What is QUOTENAME() for?

QUOTENAME() wraps an identifier in brackets, like [Order Details], which helps prevent a dynamically built object name from being misinterpreted or maliciously broken out of, when a truly dynamic identifier is unavoidable.

Key Takeaways

Dynamic SQL builds and executes a SQL statement as a string at runtime.
Concatenating untrusted input into that string creates a SQL injection risk.
sp_executesql safely parameterizes dynamic SQL, just like a parameterized query from application code.
Only values can be parameterized this way - identifiers need validation and QUOTENAME().
Use dynamic SQL only when a static, parameterized query genuinely cannot express what you need.

Summary

Dynamic SQL is a useful escape hatch for queries whose shape must vary at runtime, as long as you always parameterize values through sp_executesql rather than concatenating them. Next, you will look at security and permissions - controlling exactly who can do what inside SQL Server.

Next Lesson →