Dynamic SQL in SQL Server

Dynamic SQL is T-SQL code that is built as a string at runtime and then executed. It is useful when the structure of a query must change based on user input, such as variable table names, dynamic column lists, or flexible search filters. However, dynamic SQL requires careful handling to avoid SQL injection vulnerabilities and performance issues.

EXEC vs sp_executesql

There are two ways to execute dynamic SQL. EXEC(@sql) is simpler but does not support parameters. sp_executesql supports parameterized queries, which are safer and allow plan reuse.

-- Simple EXEC (no parameters - avoid for user input)
DECLARE @sql NVARCHAR(500);
SET @sql = N'SELECT TOP 10 * FROM Employees ORDER BY HireDate DESC';
EXEC(@sql);

-- sp_executesql with parameters (preferred)
DECLARE @sql NVARCHAR(500);
DECLARE @params NVARCHAR(200);

SET @sql = N'SELECT * FROM Employees WHERE Department = @Dept AND Salary > @MinSal';
SET @params = N'@Dept NVARCHAR(50), @MinSal DECIMAL(10,2)';

EXEC sp_executesql @sql, @params,
    @Dept = N'Engineering',
    @MinSal = 80000;

Dynamic Table and Column Names

Parameters cannot be used for table names, column names, or other identifiers. For these, you must concatenate the name into the string. Always validate identifiers with QUOTENAME to prevent injection.

DECLARE @TableName NVARCHAR(128) = N'Employees';
DECLARE @ColumnName NVARCHAR(128) = N'Department';
DECLARE @sql NVARCHAR(500);

-- QUOTENAME wraps identifiers in brackets, preventing injection
SET @sql = N'SELECT * FROM ' + QUOTENAME(@TableName)
         + N' ORDER BY ' + QUOTENAME(@ColumnName);

EXEC sp_executesql @sql;

Building a Dynamic Search

A common use case is building a WHERE clause dynamically based on which filters the user provides. This pattern combines optional parameters with parameterized execution.

CREATE PROCEDURE usp_SearchEmployees
    @FirstName NVARCHAR(50) = NULL,
    @Department NVARCHAR(50) = NULL,
    @MinSalary DECIMAL(10,2) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM Employees WHERE 1=1';
    DECLARE @params NVARCHAR(500) = N'@pFirst NVARCHAR(50), @pDept NVARCHAR(50), @pMinSal DECIMAL(10,2)';

    IF @FirstName IS NOT NULL
        SET @sql += N' AND FirstName LIKE @pFirst + ''%''';

    IF @Department IS NOT NULL
        SET @sql += N' AND Department = @pDept';

    IF @MinSalary IS NOT NULL
        SET @sql += N' AND Salary >= @pMinSal';

    SET @sql += N' ORDER BY LastName';

    EXEC sp_executesql @sql, @params,
        @pFirst = @FirstName,
        @pDept = @Department,
        @pMinSal = @MinSalary;
END;

-- Usage
EXEC usp_SearchEmployees @Department = 'Sales', @MinSalary = 50000;

Capturing Output from Dynamic SQL

Use output parameters with sp_executesql to capture scalar values from dynamic queries.

DECLARE @TableName NVARCHAR(128) = N'Orders';
DECLARE @RowCount INT;
DECLARE @sql NVARCHAR(200);

SET @sql = N'SELECT @cnt = COUNT(*) FROM ' + QUOTENAME(@TableName);

EXEC sp_executesql @sql, N'@cnt INT OUTPUT', @cnt = @RowCount OUTPUT;

SELECT @TableName AS TableName, @RowCount AS RowCount;

Debugging Dynamic SQL

Always print your dynamic SQL before executing to verify it looks correct. This is the simplest and most effective debugging technique.

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM Employees WHERE Department = @Dept';

-- Print to review before executing
PRINT @sql;

-- Or use SELECT for longer strings (PRINT truncates at 4000 chars)
SELECT @sql AS GeneratedSQL;

Security Best Practices

Dynamic SQL opens the door to SQL injection if not handled properly. Follow these rules to keep your code secure:

• Always use sp_executesql with parameters for user-supplied values
• Use QUOTENAME() for dynamic identifiers (table/column names)
• Validate identifier names against sys.tables or sys.columns before using them
• Never concatenate raw user input directly into SQL strings
• Grant EXECUTE permission on stored procedures instead of direct table access

Dynamic SQL is a powerful technique when the query structure itself must be flexible. By consistently using sp_executesql with parameters and QUOTENAME for identifiers, you get the flexibility of dynamic queries with the safety of parameterized execution.