Dynamic SQL is a powerful programming technique in SQL Server that allows you to build a Transact-SQL string at runtime and then execute it. It is essential for scenarios where parts of your query—such as table names, column names, or search conditions—are unknown until the script actually runs.

Why Use Dynamic SQL?

In standard T-SQL, you cannot use a variable for “identifiers” like table or column names. For example, SELECT * FROM @TableName will fail. Dynamic SQL solves this by turning the command into a string first.

Common use cases:

  • Dynamic Search: Building a WHERE clause based on which filters a user selects.
  • Administrative Tasks: Automating index rebuilds or backups for all databases.
  • Pivot Reports: Creating reports where the number of columns changes based on the data.

The Two Execution Methods

There are two ways to execute a dynamic string. sp_executesql is the industry standard and should be used in almost all cases.

Method A: EXEC()

This is the simplest form but is generally discouraged because it does not support parameterization, making it vulnerable to SQL Injection.

SQL
DECLARE @TableName NVARCHAR(128) = 'Employees';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM ' + @TableName;
EXEC(@SQL);

Method B: sp_executesql (The Best Practice)

This system stored procedure supports parameters. This allows SQL Server to reuse execution plans and, more importantly, protects your database from malicious input.

Example with Input Parameters:

SQL
DECLARE @SQL NVARCHAR(MAX);
DECLARE @MinSalary MONEY = 50000;
DECLARE @DeptID INT = 5;

-- The query string uses placeholders (@pMin, @pDept)
SET @SQL = N'SELECT * FROM Employees 
             WHERE Salary > @pMin AND DepartmentID = @pDept';

-- Execute with parameter definitions and assignments
EXEC sp_executesql 
    @SQL, 
    N'@pMin MONEY, @pDept INT', 
    @pMin = @MinSalary, 
    @pDept = @DeptID;

Handling Output Parameters

You can also use dynamic SQL to retrieve a single value and pass it back to your main script using the OUTPUT keyword.

Example:

SQL
DECLARE @SQL NVARCHAR(MAX);
DECLARE @TotalCount INT;

SET @SQL = N'SELECT @CountOUT = COUNT(*) FROM Employees';

EXEC sp_executesql 
    @SQL, 
    N'@CountOUT INT OUTPUT', 
    @CountOUT = @TotalCount OUTPUT;

SELECT @TotalCount AS FinalCount;

Dealing with Identifiers (QUOTENAME)

When building strings with table or column names, you must protect your code against SQL Injection and handle names with spaces (e.g., [My Table]). The QUOTENAME() function wraps your string in brackets safely.

Example:

SQL
DECLARE @Table NVARCHAR(128) = 'Order Details'; -- Has a space
DECLARE @SQL NVARCHAR(MAX);

-- Bad: 'SELECT * FROM Order Details' (Fails)
-- Good: 'SELECT * FROM [Order Details]'
SET @SQL = N'SELECT TOP 5 * FROM ' + QUOTENAME(@Table);

EXEC sp_executesql @SQL;

Building Dynamic Search Filters

One of the most practical uses of Dynamic SQL is a search procedure where some inputs might be NULL.

SQL
DECLARE @NameFilter NVARCHAR(50) = 'John';
DECLARE @CityFilter NVARCHAR(50) = NULL; -- User didn't provide a city
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Customers WHERE 1=1';

-- Dynamically append filters
IF @NameFilter IS NOT NULL
    SET @SQL = @SQL + N' AND ContactName LIKE @Name';

IF @CityFilter IS NOT NULL
    SET @SQL = @SQL + N' AND City = @City';

-- Execute with parameters
EXEC sp_executesql @SQL, 
     N'@Name NVARCHAR(50), @City NVARCHAR(50)', 
     @Name = @NameFilter, 
     @City = @CityFilter;

Security: The Threat of SQL Injection

Dynamic SQL is the #1 vector for SQL Injection attacks. If you concatenate user input directly into a string, a hacker can drop your tables or steal data.

  • Bad (Vulnerable): SET @SQL = 'SELECT * FROM Users WHERE Name = ''' + @UserInput + ''''
  • Good (Secure): Use sp_executesql with parameters.
  • Handling Object Names: Since you can’t parameterize a table name, use the QUOTENAME() function to wrap the table name in brackets safely.

Secure Table Selection:

SQL
DECLARE @Table NVARCHAR(128) = 'Users; DROP TABLE Orders;--'; -- Malicious input
DECLARE @SQL NVARCHAR(MAX);

-- QUOTENAME ensures the input is treated as a single object name [Users; DROP TABLE Orders;--]
SET @SQL = N'SELECT TOP 10 * FROM ' + QUOTENAME(@Table);
EXEC sp_executesql @SQL;

Categorized in:

Stored Procedures,