What is `sp_executesql`?

Every SQL Server developer reaches a point where they need to build a SQL query at runtime — where the table name, column list, or filter condition is not known until execution time.

The naive approach is string concatenation with EXEC(). It works, but it opens the door to SQL injection, kills plan reuse, and creates maintenance headaches.

sp_executesql is the professional, parameterized alternative. It lets you execute dynamic SQL while passing parameters safely — exactly like a prepared statement in application code.

Syntax

EXEC sp_executesql
    @Statement   ,          -- The dynamic SQL string (NVARCHAR)
    @ParamDef    ,          -- Parameter definitions (like a signature)
    @Param1 = value1,       -- Actual parameter values
    @Param2 = value2;

Three parts — statement, definition, values. We'll break each one down.

Setting Up the Demo

We will use a single table variable throughout all examples so you can run everything without creating any physical objects.

-- ============================================
-- Demo table used in ALL examples below
-- ============================================

DECLARE @Orders TABLE (
    OrderID       INT,
    CustomerName  NVARCHAR(100),
    Product       NVARCHAR(100),
    Quantity      INT,
    UnitPrice     DECIMAL(10,2),
    OrderDate     DATE,
    Status        NVARCHAR(20),
    Region        NVARCHAR(50)
);

INSERT INTO @Orders
VALUES
(1001, 'Aarav Sharma',   'Laptop',      2, 55000.00, '2024-01-05', 'Shipped',    'North'),
(1002, 'Priya Mehta',    'Mouse',       5,   850.00, '2024-01-08', 'Delivered',  'South'),
(1003, 'Rohan Verma',    'Keyboard',    3,  1200.00, '2024-01-12', 'Pending',    'East'),
(1004, 'Sneha Joshi',    'Monitor',     1, 18000.00, '2024-01-15', 'Shipped',    'West'),
(1005, 'Arjun Patel',    'Laptop',      1, 55000.00, '2024-02-01', 'Delivered',  'North'),
(1006, 'Kavya Nair',     'Webcam',      4,  2500.00, '2024-02-10', 'Pending',    'South'),
(1007, 'Vikram Singh',   'Headphones',  2,  3200.00, '2024-02-14', 'Shipped',    'East'),
(1008, 'Deepika Rao',    'Mouse',       6,   850.00, '2024-02-20', 'Delivered',  'West'),
(1009, 'Ankit Gupta',    'Laptop',      3, 55000.00, '2024-03-03', 'Shipped',    'North'),
(1010, 'Neha Tiwari',    'Monitor',     2, 18000.00, '2024-03-07', 'Pending',    'South'),
(1011, 'Rahul Kumar',    'Keyboard',    5,  1200.00, '2024-03-11', 'Delivered',  'East'),
(1012, 'Pooja Desai',    'Webcam',      1,  2500.00, '2024-03-15', 'Shipped',    'West'),
(1013, 'Karan Malhotra', 'Laptop',      1, 55000.00, '2024-04-02', 'Delivered',  'North'),
(1014, 'Aisha Khan',     'Headphones',  3,  3200.00, '2024-04-09', 'Pending',    'South'),
(1015, 'Suresh Iyer',    'Mouse',       8,   850.00, '2024-04-18', 'Shipped',    'East'),
(1016, 'Meera Pillai',   'Laptop',      2, 55000.00, '2024-05-01', 'Delivered',  'West'),
(1017, 'Ravi Shankar',   'Monitor',     3, 18000.00, '2024-05-08', 'Shipped',    'North'),
(1018, 'Swati Bose',     'Keyboard',    2,  1200.00, '2024-05-22', 'Pending',    'South'),
(1019, 'Dev Kapoor',     'Webcam',      5,  2500.00, '2024-06-04', 'Delivered',  'East'),
(1020, 'Nisha Pillai',   'Laptop',      1, 55000.00, '2024-06-15', 'Shipped',    'West');

Example 1 — The Simplest Call

Before going dynamic, understand the basic structure with a hardcoded statement:

-- ============================================
-- Simplest possible sp_executesql call
-- ============================================

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT TOP 5 OrderID, CustomerName, Product, Status
             FROM Orders
             ORDER BY OrderID;';

EXEC sp_executesql @SQL;

No parameters here — just executing a plain string. Not very useful yet, but shows the basic syntax.

Example 2 — One Parameter (The Core Pattern)

Now let's add a parameter. This is what separates sp_executesql from raw EXEC:

-- ============================================
-- Filter orders by Status — one parameter
-- ============================================

DECLARE @SQL      NVARCHAR(MAX);
DECLARE @ParamDef NVARCHAR(500);
DECLARE @Status   NVARCHAR(20) = 'Shipped';

-- Step 1: Write the SQL with @Status as a placeholder
SET @SQL = N'
    SELECT OrderID, CustomerName, Product, Quantity, UnitPrice, Status
    FROM Orders
    WHERE Status = @Status
    ORDER BY OrderID;
';

-- Step 2: Define the parameter (name + data type)
SET @ParamDef = N'@Status NVARCHAR(20)';

-- Step 3: Execute — pass the actual value
EXEC sp_executesql
    @SQL,
    @ParamDef,
    @Status = @Status;

Notice: @Status appears in three places:

Inside @SQL as a placeholder WHERE Status = @Status
Inside @ParamDef as a type declaration @Status NVARCHAR(20)
In the final call as the actual value @Status = @Status

This is the exact same pattern you used in your archive SP — just with @BatchId, @ArchiveUpTo, and @BatchSize.

Example 3 — Multiple Parameters

Now exactly the pattern from the question — three parameters of different types:

-- ============================================
-- Archive-style pattern: BatchId, DateRange, BatchSize
-- ============================================

DECLARE @SQL         NVARCHAR(MAX);
DECLARE @ParamDef    NVARCHAR(1000);

-- The three parameters
DECLARE @BatchId     UNIQUEIDENTIFIER = NEWID();
DECLARE @ArchiveUpTo DATETIME         = '2024-03-31 23:59:59';
DECLARE @BatchSize   INT              = 5;

-- Step 1: Dynamic SQL with all three as placeholders
SET @SQL = N'
    SELECT TOP (@BatchSize)
        OrderID,
        CustomerName,
        Product,
        OrderDate,
        Status,
        @BatchId AS BatchReference
    FROM Orders
    WHERE OrderDate <= @ArchiveUpTo
      AND Status    =  ''Delivered''
    ORDER BY OrderDate ASC;
';

-- Step 2: Define ALL parameters in one string (comma-separated)
SET @ParamDef = N'@BatchId     UNIQUEIDENTIFIER,
                  @ArchiveUpTo DATETIME,
                  @BatchSize   INT';

-- Step 3: Execute — name each parameter explicitly
EXEC sp_executesql
    @SQL,
    @ParamDef,
    @BatchId     = @BatchId,
    @ArchiveUpTo = @ArchiveUpTo,
    @BatchSize   = @BatchSize;

This is almost word-for-word the snippet from your question — broken down so every line is clear.

Example 4 — OUTPUT Parameters

sp_executesql can also return values back through OUTPUT parameters. Very useful for getting a count or generated ID from dynamic SQL:

-- ============================================
-- Get count of orders matching criteria — OUTPUT param
-- ============================================

DECLARE @SQL         NVARCHAR(MAX);
DECLARE @ParamDef    NVARCHAR(500);
DECLARE @Region      NVARCHAR(50)  = 'North';
DECLARE @TotalOrders INT;            -- This will be populated by the dynamic SQL

SET @SQL = N'
    SELECT @TotalOrders = COUNT(*)
    FROM Orders
    WHERE Region = @Region;
';

SET @ParamDef = N'@Region      NVARCHAR(50),
                  @TotalOrders INT OUTPUT';   -- OUTPUT keyword here

EXEC sp_executesql
    @SQL,
    @ParamDef,
    @Region      = @Region,
    @TotalOrders = @TotalOrders OUTPUT;       -- OUTPUT keyword here too

-- Use the returned value
SELECT @TotalOrders AS TotalNorthOrders;

-- Result: 5 (OrderIDs 1001, 1005, 1009, 1013, 1017)

OUTPUT appears in both @ParamDef and the final call. Miss either one and it won't work.

Example 5 — Dynamic WHERE Clause Builder

A very common real-world pattern — optional filters where the user may or may not provide values:

-- ============================================
-- Search orders with optional filters
-- Filters: Region, Status, MinPrice (all optional)
-- ============================================

DECLARE @SQL         NVARCHAR(MAX);
DECLARE @ParamDef    NVARCHAR(1000);
DECLARE @WhereClause NVARCHAR(2000) = N' WHERE 1=1 ';

-- User-provided filters (NULL means "not filtered")
DECLARE @Region   NVARCHAR(50)   = 'South';   -- Filter by South region
DECLARE @Status   NVARCHAR(20)   = NULL;      -- No status filter
DECLARE @MinPrice DECIMAL(10,2)  = 1000.00;   -- Only orders > 1000

-- Build WHERE clause dynamically — only add conditions that have values
IF @Region   IS NOT NULL  SET @WhereClause += N' AND Region    = @Region   ';
IF @Status   IS NOT NULL  SET @WhereClause += N' AND Status    = @Status   ';
IF @MinPrice IS NOT NULL  SET @WhereClause += N' AND UnitPrice >= @MinPrice ';

-- Build the full query
SET @SQL = N'
    SELECT OrderID, CustomerName, Product, UnitPrice, Region, Status
    FROM Orders'
    + @WhereClause
    + N' ORDER BY OrderID;';

-- Always define ALL possible parameters — unused ones are simply ignored
SET @ParamDef = N'@Region   NVARCHAR(50),
                  @Status   NVARCHAR(20),
                  @MinPrice DECIMAL(10,2)';

EXEC sp_executesql
    @SQL,
    @ParamDef,
    @Region   = @Region,
    @Status   = @Status,
    @MinPrice = @MinPrice;

Key insight: Even though @Status is NULL and wasn't added to the WHERE clause, you still pass it in the parameter list. SQL Server simply ignores unused parameters — no error.

Example 6 — Dynamic ORDER BY (Column Name as Variable)

One thing you cannot parameterize is object names — table names, column names, schema names. Those must be concatenated. But you can still keep the values safe:

-- ============================================
-- Dynamic ORDER BY with safe column name handling
-- ============================================

DECLARE @SQL        NVARCHAR(MAX);
DECLARE @ParamDef   NVARCHAR(500);

-- These come from user input — must be validated, not parameterized
DECLARE @SortColumn NVARCHAR(50) = 'UnitPrice';
DECLARE @SortDir    NVARCHAR(4)  = 'DESC';

-- These are values — safe to parameterize
DECLARE @Region     NVARCHAR(50) = 'East';
DECLARE @TopN       INT          = 5;

-- Whitelist validation — NEVER trust user input for column/object names
IF @SortColumn NOT IN ('OrderID','CustomerName','Product','UnitPrice','OrderDate')
    SET @SortColumn = 'OrderID';   -- Fallback to safe default

IF @SortDir NOT IN ('ASC','DESC')
    SET @SortDir = 'ASC';

-- Column name is concatenated (no choice), values are parameterized (safe)
SET @SQL = N'
    SELECT TOP (@TopN)
        OrderID, CustomerName, Product, UnitPrice, Region
    FROM Orders
    WHERE Region = @Region
    ORDER BY ' + QUOTENAME(@SortColumn) + ' ' + @SortDir + ';';
--            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
--            QUOTENAME wraps in [brackets] to prevent injection via column name

SET @ParamDef = N'@Region NVARCHAR(50),
                  @TopN   INT';

EXEC sp_executesql
    @SQL,
    @ParamDef,
    @Region = @Region,
    @TopN   = @TopN;

QUOTENAME() wraps the column name in [square brackets], neutralizing any injection attempt even if the whitelist is bypassed. Always use it on concatenated identifiers.

Example 7 — Batch Archive Pattern (Your Original Use Case)

This is the full production-ready version of the pattern in your question — dynamic batched archiving:

-- ============================================
-- Batched Archive Pattern
-- Matches exactly what you wrote in your SP
-- ============================================

DECLARE @SQL         NVARCHAR(MAX);
DECLARE @ParamDef    NVARCHAR(500);
DECLARE @BatchId     UNIQUEIDENTIFIER = NEWID();
DECLARE @ArchiveUpTo DATETIME         = '2024-04-30 23:59:59';
DECLARE @BatchSize   INT              = 5;
DECLARE @RowsAffected INT             = 0;
DECLARE @TotalArchived INT            = 0;

-- In a real SP, this would be a loop.
-- Here we show a single iteration clearly.

SET @SQL = N'
    INSERT INTO OrdersArchive         -- your archive table
        (OrderID, CustomerName, Product, Quantity,
         UnitPrice, OrderDate, Status, Region, BatchId, ArchivedOn)

    SELECT TOP (@BatchSize)
        OrderID, CustomerName, Product, Quantity,
        UnitPrice, OrderDate, Status, Region,
        @BatchId,
        GETDATE()
    FROM Orders
    WHERE OrderDate <= @ArchiveUpTo
      AND Status = ''Delivered''
    ORDER BY OrderDate ASC;
';

-- Parameter definition — exact types matter
SET @ParamDef = N'@BatchId     UNIQUEIDENTIFIER,
                  @ArchiveUpTo DATETIME,
                  @BatchSize   INT';

-- Execute — named parameter passing (order does not matter when named)
EXEC sp_executesql
    @SQL,
    @ParamDef,
    @BatchId     = @BatchId,
    @ArchiveUpTo = @ArchiveUpTo,
    @BatchSize   = @BatchSize;

SET @RowsAffected  = @@ROWCOUNT;
SET @TotalArchived = @TotalArchived + @RowsAffected;

PRINT 'Batch ' + CAST(@BatchId AS VARCHAR(36))
    + ' archived ' + CAST(@RowsAffected AS VARCHAR(10)) + ' rows.';

`sp_executesql` vs `EXEC()` — The Full Comparison

This is something every SQL Server developer must understand clearly:

-- =============================================
-- EXEC() — The unsafe, plan-unfriendly way
-- =============================================

DECLARE @Status   NVARCHAR(20) = 'Shipped';
DECLARE @BadSQL   NVARCHAR(MAX);

-- Value is concatenated directly into the string
SET @BadSQL = N'SELECT * FROM Orders WHERE Status = ''' + @Status + '''';

EXEC (@BadSQL);

-- What if @Status = 'x'' OR ''1''=''1' ?
-- The query becomes:
-- SELECT * FROM Orders WHERE Status = 'x' OR '1'='1'
-- Returns ALL rows — SQL Injection!


-- =============================================
-- sp_executesql — The safe, plan-friendly way
-- =============================================

DECLARE @GoodSQL  NVARCHAR(MAX);
DECLARE @ParamDef NVARCHAR(200);

SET @GoodSQL  = N'SELECT * FROM Orders WHERE Status = @Status';
SET @ParamDef = N'@Status NVARCHAR(20)';

EXEC sp_executesql @GoodSQL, @ParamDef, @Status = @Status;

-- Even if @Status = 'x'' OR ''1''=''1'
-- SQL Server treats the entire thing as a literal string value
-- No injection possible — the parameter is never interpreted as SQL

Feature	`EXEC()`	`sp_executesql`
SQL Injection safe	❌ No	✅ Yes
Plan cache reuse	❌ No (new plan every time)	✅ Yes (same plan for same SQL shape)
OUTPUT parameters	❌ Not supported	✅ Supported
Passes parameter types	❌ No	✅ Yes
Unicode support	Limited	✅ Full NVARCHAR
Recommended for production	❌ Avoid	✅ Always prefer

Plan Cache Reuse — The Hidden Performance Win

When you use sp_executesql with parameters, SQL Server caches the execution plan for the query shape, not for specific values. The next call with different parameter values reuses the same plan — no recompilation.

-- First call — plan compiled and cached
EXEC sp_executesql
    N'SELECT * FROM Orders WHERE Region = @Region',
    N'@Region NVARCHAR(50)',
    @Region = 'North';

-- Second call — SAME plan reused from cache, just different value
EXEC sp_executesql
    N'SELECT * FROM Orders WHERE Region = @Region',
    N'@Region NVARCHAR(50)',
    @Region = 'South';

With raw EXEC() and string concatenation, each unique value produces a unique SQL string, which means a unique cache entry. On a busy server, this bloats the plan cache with thousands of single-use plans — a classic cause of memory pressure.

You can verify plan reuse with:

-- Check plan cache for your dynamic SQL
SELECT 
    qs.execution_count,
    qs.total_worker_time / 1000        AS CPU_ms,
    qs.total_elapsed_time / 1000       AS Duration_ms,
    SUBSTRING(st.text, 1, 200)         AS SQLText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%Orders%'
  AND st.text LIKE '%@Region%'
ORDER BY qs.execution_count DESC;

Common Mistakes and How to Avoid Them

Mistake 1 — Using VARCHAR instead of NVARCHAR

-- WRONG
DECLARE @SQL VARCHAR(MAX);
SET @SQL = 'SELECT ...';
EXEC sp_executesql @SQL;   -- Error: sp_executesql requires NVARCHAR

-- CORRECT
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'SELECT ...';  -- N prefix = Unicode literal

sp_executesql strictly requires NVARCHAR. Missing the N prefix on the literal also matters — N'SELECT ...' is Unicode, 'SELECT ...' is not.

Mistake 2 — Wrong Parameter Definition Syntax

-- WRONG — missing data type, wrong separator
SET @ParamDef = N'@Status, @Region';

-- WRONG — semicolons used instead of commas
SET @ParamDef = N'@Status NVARCHAR(20); @Region NVARCHAR(50)';

-- CORRECT — comma-separated, name + type for each
SET @ParamDef = N'@Status NVARCHAR(20), @Region NVARCHAR(50)';

Mistake 3 — Forgetting OUTPUT in Both Places

-- WRONG — OUTPUT in definition but missing in the call
SET @ParamDef = N'@Count INT OUTPUT';
EXEC sp_executesql @SQL, @ParamDef, @Count = @Count;  -- Missing OUTPUT keyword

-- CORRECT — OUTPUT must appear in BOTH places
EXEC sp_executesql @SQL, @ParamDef, @Count = @Count OUTPUT;

Mistake 4 — Concatenating Values Instead of Parameterizing

-- WRONG — defeats the entire purpose
SET @SQL = N'SELECT * FROM Orders WHERE Region = ''' + @Region + '''';

-- CORRECT — parameter in placeholder
SET @SQL = N'SELECT * FROM Orders WHERE Region = @Region';

Mistake 5 — Trying to Parameterize Object Names

-- WRONG — table names cannot be parameters
SET @SQL      = N'SELECT * FROM @TableName';
SET @ParamDef = N'@TableName NVARCHAR(128)';
-- This executes as: SELECT * FROM '@TableName' — treated as a string, not a table

-- CORRECT — concatenate + QUOTENAME for object names
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName);
-- Then validate @TableName against a whitelist before using it

Debugging Tips for Dynamic SQL

When your sp_executesql call breaks, the fastest way to debug is to print the generated SQL:

-- Always PRINT before EXEC during development
DECLARE @SQL      NVARCHAR(MAX);
DECLARE @ParamDef NVARCHAR(500);
DECLARE @Region   NVARCHAR(50) = 'North';
DECLARE @TopN     INT          = 10;

SET @SQL = N'
    SELECT TOP (@TopN) OrderID, CustomerName, Region
    FROM Orders
    WHERE Region = @Region
    ORDER BY OrderID;';

SET @ParamDef = N'@Region NVARCHAR(50), @TopN INT';

-- Debug: see exact SQL being executed
PRINT @SQL;
PRINT @ParamDef;

-- Then execute
EXEC sp_executesql @SQL, @ParamDef,
    @Region = @Region,
    @TopN   = @TopN;

For very long SQL strings where PRINT truncates (8000 char limit), use this trick:

-- Print long SQL without truncation
SELECT CAST(@SQL AS XML);  -- Shows full string in SSMS results grid
-- OR
SELECT @SQL;               -- Just SELECT it

Quick Reference

-- Bare minimum
EXEC sp_executesql @SQL;

-- One parameter
EXEC sp_executesql @SQL, N'@P1 INT', @P1 = 10;

-- Multiple parameters
EXEC sp_executesql @SQL,
    N'@P1 INT, @P2 NVARCHAR(50), @P3 DATETIME',
    @P1 = 1,
    @P2 = 'Value',
    @P3 = '2024-01-01';

-- With OUTPUT
EXEC sp_executesql @SQL,
    N'@In NVARCHAR(50), @Out INT OUTPUT',
    @In  = 'North',
    @Out = @ResultVar OUTPUT;

-- Named parameters — order does NOT matter
EXEC sp_executesql @SQL, @ParamDef,
    @BatchSize   = @BatchSize,      -- These three lines can be in any order
    @ArchiveUpTo = @ArchiveUpTo,
    @BatchId     = @BatchId;

Summary

sp_executesql is not just a way to run dynamic SQL — it is the only correct way for production SQL Server code. The three-part call structure (statement, definition, values) is the foundation of safe dynamic SQL:

SQL injection is eliminated because values are never interpreted as SQL code
Plan cache works efficiently because the SQL shape stays constant across calls
OUTPUT parameters allow values to flow back from dynamic SQL into your outer procedure
Debugging is straightforward — just print the SQL and param definition before executing

Every time you feel the urge to write EXEC ('SELECT ... WHERE col = ''' + @val + ''''), stop and reach for sp_executesql instead. Your future self, your security team, and your DBA will all thank you.

What is sp_executesql?

Results for "DynamicSQL"

sp_executesql in SQL Server