Dynamic SQL in SQL Server

Dynamic SQL = building a SQL statement as a STRING and executing it.

EXEC (@sql)                          -> simple, NO parameters (risky)
EXEC sys.sp_executesql @sql, N'@p INT', @p = 5   -> parameterized (SAFE + plan reuse)

Use it when table names, column names, sort order or the list of
filters are only known at run time.
GOLDEN RULES:
  1) Pass VALUES as parameters (sp_executesql), never concatenate them.
  2) For object names use QUOTENAME() and validate against sys tables.
  3) PRINT @sql while developing to see the generated statement.

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic tables: yearly archive tables (same structure, different names)

DROP TABLE IF EXISTS dbo.OrdersArchive_2024;
DROP TABLE IF EXISTS dbo.OrdersArchive_2025;

SELECT * INTO dbo.OrdersArchive_2024 FROM dbo.Orders WHERE YEAR(OrderDate) = 2024;
SELECT * INTO dbo.OrdersArchive_2025 FROM dbo.Orders WHERE YEAR(OrderDate) = 2025;
GO

(54 rows affected)
(26 rows affected)

Part A: EXEC vs sp_executesql

Simplest dynamic SQL

DECLARE @sql NVARCHAR(MAX) = N'SELECT TOP (5) ProductName, UnitPrice FROM dbo.Products ORDER BY UnitPrice DESC;';
EXEC (@sql);
GO

Parameterized with sp_executesql (correct way to pass values)

DECLARE @sql NVARCHAR(MAX) = N'
    SELECT EmployeeID, FirstName, Salary
    FROM dbo.Employees
    WHERE DepartmentID = @DeptID AND Salary >= @MinSal;';

EXEC sys.sp_executesql
     @sql,
     N'@DeptID INT, @MinSal DECIMAL(10,2)',     -- parameter definitions
     @DeptID = 2, @MinSal = 50000;              -- parameter values
GO

OUTPUT parameter from dynamic SQL

DECLARE @sql NVARCHAR(MAX) = N'SELECT @Cnt = COUNT(*) FROM dbo.Orders WHERE Status = @Status;';
DECLARE @Count INT;

EXEC sys.sp_executesql @sql, N'@Status VARCHAR(20), @Cnt INT OUTPUT', @Status = 'Delivered', @Cnt = @Count OUTPUT;
SELECT @Count AS DeliveredOrders;
GO

Part B: Dynamic Table Name (safe way)

DECLARE @Year INT = 2024;
DECLARE @TableName SYSNAME = N'OrdersArchive_' + CAST(@Year AS NVARCHAR(4));
DECLARE @sql NVARCHAR(MAX);

-- Validate that the table really exists (prevents injection through the name)
IF OBJECT_ID(N'dbo.' + QUOTENAME(@TableName), N'U') IS NULL
BEGIN
    RAISERROR('Table %s does not exist.', 16, 1, @TableName);
END
ELSE
BEGIN
    SET @sql = N'SELECT Status, COUNT(*) AS Orders FROM dbo.' + QUOTENAME(@TableName) + N' GROUP BY Status;';
    PRINT @sql;
    EXEC sys.sp_executesql @sql;
END;
GO

SELECT Status, COUNT(*) AS Orders FROM dbo.[OrdersArchive_2024] GROUP BY Status;

Run the same query on Every archive table (loop over sys.tables)

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + N'SELECT ''' + name + N''' AS SourceTable, COUNT(*) AS TotalOrders FROM dbo.'
                    + QUOTENAME(name) + N' UNION ALL '
FROM sys.tables
WHERE name LIKE N'OrdersArchive[_]%';

SET @sql = LEFT(@sql, LEN(@sql) - LEN(N' UNION ALL')) + N';';   -- remove the last UNION ALL
PRINT @sql;
EXEC sys.sp_executesql @sql;
GO

SELECT 'OrdersArchive_2024' AS SourceTable, COUNT(*) AS TotalOrders FROM dbo.[OrdersArchive_2024] UNION ALL SELECT 'OrdersArchive_2025' AS SourceTable, COUNT(*) AS TotalOrders FROM dbo.[OrdersArchive_2025];

Part C: Dynamic Search Procedure (only add filters that were passed)

CREATE OR ALTER PROCEDURE dbo.usp_DynamicOrderSearch
    @CustomerID INT          = NULL,
    @Status     VARCHAR(20)  = NULL,
    @FromDate   DATE         = NULL,
    @ToDate     DATE         = NULL,
    @City       VARCHAR(50)  = NULL,
    @SortColumn SYSNAME      = N'OrderDate',
    @SortDesc   BIT          = 1,
    @Debug      BIT          = 0
AS
BEGIN
    SET NOCOUNT ON;

    -- Whitelist sort columns
    IF @SortColumn NOT IN (N'OrderID', N'OrderDate', N'Status', N'ShipCity', N'CustomerID')
        SET @SortColumn = N'OrderDate';

    DECLARE @sql NVARCHAR(MAX) = N'
SELECT o.OrderID, o.OrderDate, o.Status, o.ShipCity, c.CustomerName
FROM dbo.Orders o
JOIN dbo.Customers c ON c.CustomerID = o.CustomerID
WHERE 1 = 1';

    IF @CustomerID IS NOT NULL SET @sql += N' AND o.CustomerID = @CustomerID';
    IF @Status     IS NOT NULL SET @sql += N' AND o.Status = @Status';
    IF @FromDate   IS NOT NULL SET @sql += N' AND o.OrderDate >= @FromDate';
    IF @ToDate     IS NOT NULL SET @sql += N' AND o.OrderDate <= @ToDate';
    IF @City       IS NOT NULL SET @sql += N' AND o.ShipCity = @City';

    SET @sql += N' ORDER BY o.' + QUOTENAME(@SortColumn) + CASE WHEN @SortDesc = 1 THEN N' DESC' ELSE N' ASC' END + N';';

    IF @Debug = 1 PRINT @sql;

    EXEC sys.sp_executesql @sql,
         N'@CustomerID INT, @Status VARCHAR(20), @FromDate DATE, @ToDate DATE, @City VARCHAR(50)',
         @CustomerID, @Status, @FromDate, @ToDate, @City;
END;
GO

EXEC dbo.usp_DynamicOrderSearch @Status = 'Pending', @Debug = 1;
EXEC dbo.usp_DynamicOrderSearch @City = 'Hyderabad', @FromDate = '2025-01-01', @SortColumn = 'OrderID', @SortDesc = 0, @Debug = 1;
EXEC dbo.usp_DynamicOrderSearch @CustomerID = 2;
GO

SELECT o.OrderID, o.OrderDate, o.Status, o.ShipCity, c.CustomerName
FROM dbo.Orders o
JOIN dbo.Customers c ON c.CustomerID = o.CustomerID
WHERE 1 = 1 AND o.Status = @Status ORDER BY o.[OrderDate] DESC;

SELECT o.OrderID, o.OrderDate, o.Status, o.ShipCity, c.CustomerName
FROM dbo.Orders o
JOIN dbo.Customers c ON c.CustomerID = o.CustomerID
WHERE 1 = 1 AND o.OrderDate >= @FromDate AND o.ShipCity = @City ORDER BY o.[OrderID] ASC;

Part D: SQL Injection - why concatenating values is dangerous

-- Imagine @UserInput comes from a web page text box.
DECLARE @UserInput NVARCHAR(100) = N'Mumbai'' OR 1=1 --';
DECLARE @sql NVARCHAR(MAX);

-- BAD : value glued into the string -> attacker returns ALL customers
SET @sql = N'SELECT CustomerID, CustomerName, City FROM dbo.Customers WHERE City = ''' + @UserInput + N'''';
PRINT N'BAD  : ' + @sql;
EXEC (@sql);

-- GOOD : value passed as a parameter -> treated as plain text, returns 0 rows
SET @sql = N'SELECT CustomerID, CustomerName, City FROM dbo.Customers WHERE City = @City';
PRINT N'GOOD : ' + @sql;
EXEC sys.sp_executesql @sql, N'@City NVARCHAR(100)', @City = @UserInput;
GO

BAD  : SELECT CustomerID, CustomerName, City FROM dbo.Customers WHERE City = 'Mumbai' OR 1=1 --'

GOOD : SELECT CustomerID, CustomerName, City FROM dbo.Customers WHERE City = @City

Part E: Dynamic Column List / Metadata Driven Queries

Count NULLs in every column of a table (data quality check)

DECLARE @Table SYSNAME = N'Customers';
DECLARE @sql NVARCHAR(MAX);

SELECT @sql = STRING_AGG(
                 CAST(N'SELECT ''' + c.name + N''' AS ColumnName, COUNT(*) - COUNT(' + QUOTENAME(c.name)
                      + N') AS NullCount FROM dbo.' + QUOTENAME(@Table) AS NVARCHAR(MAX)),
                 N' UNION ALL ')
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(N'dbo.' + QUOTENAME(@Table));

PRINT @sql;
EXEC sys.sp_executesql @sql;
GO

SELECT 'CustomerID' AS ColumnName, COUNT(*) - COUNT([CustomerID]) AS NullCount FROM dbo.[Customers] UNION ALL SELECT 'CustomerName' AS ColumnName, COUNT(*) - COUNT([CustomerName]) AS NullCount FROM dbo.[Customers] UNION ALL SELECT 'Email' AS ColumnName, COUNT(*) - COUNT([Email]) AS NullCount FROM dbo.[Customers] UNION ALL SELECT 'Phone' AS ColumnName, COUNT(*) - COUNT([Phone]) AS NullCount FROM dbo.[Customers] UNION ALL SELECT 'City' AS ColumnName, COUNT(*) - COUNT([City]) AS NullCount FROM dbo.[Customers] UNION ALL SELECT 'State' AS ColumnName, COUNT(*) - COUNT([State]) AS NullCount FROM dbo.[Customers] UNION ALL SELECT 'Segment' AS ColumnName, COUNT(*) - COUNT([Segment]) AS NullCount FROM dbo.[Customers] UNION ALL SELECT 'SignupDate' AS ColumnName, COUNT(*) - COUNT([SignupDate]) AS NullCount FROM dbo.[Customers]

Row count for every user table (no dynamic SQL needed - use catalog views)

SELECT t.name AS TableName, SUM(p.rows) AS TotalRows
FROM sys.tables t
JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0, 1)
GROUP BY t.name
ORDER BY TotalRows DESC;
GO

Part F: Scope Rules

• Dynamic SQL runs in its OWN batch/scope:
• cannot see local @variables of the caller (pass them as parameters)
• #temp tables created by the caller ARE visible inside dynamic SQL
• #temp tables created INSIDE dynamic SQL are NOT visible after it ends

DROP TABLE IF EXISTS #Outer;
CREATE TABLE #Outer (ID INT);
INSERT INTO #Outer VALUES (1), (2);

EXEC (N'SELECT COUNT(*) AS SeenFromDynamicSql FROM #Outer;');   -- works

EXEC (N'CREATE TABLE #Inner (ID INT); INSERT INTO #Inner VALUES (1);');
-- SELECT * FROM #Inner;   -- would fail: #Inner is already gone

DROP TABLE #Outer;
GO

(2 rows affected)

(1 row affected)

Practice Questions

1. Write a procedure that returns TOP N rows from any table name passed in (validate the name!).
2. Build a dynamic query that sums Sales.TotalAmount grouped by a column chosen at run time
3. Generate and run "SELECT MAX(<col>)" for every DATE column in the database.
4. Write a dynamic PIVOT of Sales: rows = Region, columns = each distinct ProductID.