Dynamic SQL Result Into a Variable with sp_executesql

To get a dynamic SQL result into a variable, declare an OUTPUT parameter and run the statement with sp_executesql. The dynamic statement assigns the value to that parameter, and the caller reads it afterward. A plain EXEC can’t do this.

Why Plain EXEC Falls Short

A dynamic statement runs in its own scope. It can’t see the variables of the batch that built it, and a variable it sets disappears when it ends. A SELECT inside it returns rows to the client, not to a variable. The system procedure sp_executesql solves both problems, because it accepts parameters in both directions.

Build the Demo Data

The demo database has three small tables. Departments holds five departments. Orders holds three orders, two of them for Marketing, and its key is a plain number. Tickets starts empty and appears near the end.

IF DB_ID(N'DynamicResultDemo') IS NULL CREATE DATABASE DynamicResultDemo;
GO
USE DynamicResultDemo;
GO
DROP TABLE IF EXISTS dbo.Orders, dbo.Departments, dbo.Tickets;
CREATE TABLE dbo.Departments (DepartmentID int NOT NULL PRIMARY KEY, DepartmentName nvarchar(50) NOT NULL);
INSERT INTO dbo.Departments (DepartmentID, DepartmentName)
VALUES (1, N'Sales'), (2, N'Support'), (3, N'Billing'), (4, N'Marketing'), (5, N'Shipping');
CREATE TABLE dbo.Orders (
    OrderID      int NOT NULL PRIMARY KEY,
    DepartmentID int NOT NULL,
    Total        decimal(10,2) NOT NULL
);
INSERT INTO dbo.Orders (OrderID, DepartmentID, Total) VALUES (1, 4, 25.00), (2, 4, 40.50), (3, 1, 99.99);
CREATE TABLE dbo.Tickets (TicketID int IDENTITY(1,1) NOT NULL PRIMARY KEY, Title nvarchar(60) NOT NULL);

Return One Value

First, the plain case. The statement is a string, and the value 4 travels in as a parameter. The result comes back as a row to the client.

DECLARE @sql nvarchar(400) = N'SELECT DepartmentName FROM dbo.Departments WHERE DepartmentID = @ID;';
EXEC sys.sp_executesql @sql, N'@ID int', @ID = 4;

Now capture the dynamic SQL result in a variable. Add a second parameter to the list, mark it OUTPUT, and assign to it inside the statement. The caller passes a variable for it, and the keyword OUTPUT appears again in the call.

DECLARE @sql nvarchar(400) = N'SELECT @Name = DepartmentName FROM dbo.Departments WHERE DepartmentID = @ID;';
DECLARE @Name nvarchar(50);
EXEC sys.sp_executesql @sql, N'@ID int, @Name nvarchar(50) OUTPUT', @ID = 4, @Name = @Name OUTPUT;
SELECT @Name AS ReturnedName;

The three pieces must agree. The statement assigns @Name. The parameter list declares @Name nvarchar(50) OUTPUT. The call passes @Name = @Name OUTPUT. Leave OUTPUT off the call, and the caller’s variable stays NULL.

The Statement Must Be nvarchar

The most common error comes from the type of the statement variable. The procedure wants nvarchar. A varchar variable fails right away, and the message points at the first parameter.

DECLARE @sql varchar(400) = 'SELECT 1;';
EXEC sys.sp_executesql @sql;

Msg 214, Level 16, State 2, Procedure sys.sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

SSMS 22 adds [Batch Start Line 0] after Line 1, as the picture shows. Declare the variable as nvarchar, and prefix literals with N. The same rule applies to the parameter definition string. The type of the OUTPUT variable has no such rule. An int, a decimal or a date works.

Counts and Other Types

Any type works as an output. A count is typical. Marketing has two orders.

DECLARE @sql nvarchar(400) = N'SELECT @n = COUNT(*) FROM dbo.Orders WHERE DepartmentID = @ID;';
DECLARE @n int;
EXEC sys.sp_executesql @sql, N'@ID int, @n int OUTPUT', @ID = 4, @n = @n OUTPUT;
SELECT @n AS OrderCount;

Only One Row Fits

A variable holds one value. When the SELECT reads several rows, each assignment overwrites the last one. The next statement has no WHERE clause.

DECLARE @sql nvarchar(400) = N'SELECT @Name = DepartmentName FROM dbo.Departments;';
DECLARE @Name nvarchar(50);
EXEC sys.sp_executesql @sql, N'@Name nvarchar(50) OUTPUT', @Name = @Name OUTPUT;
SELECT @Name AS LastRowName;

The variable ends with the value of the last row read, and without ORDER BY the row order isn’t guaranteed. Filter to one row, use TOP (1) with ORDER BY, or aggregate. An assignment from many rows keeps only the last value, and the order is not defined.

Table and Column Names

A parameter carries a value, never a name. You can’t pass a table name as a parameter, so a generic lookup builds the name into the string. QUOTENAME wraps the name in brackets and doubles any closing bracket inside it. That makes the name safe to concatenate. Values still travel as parameters, never inside the string. A value pasted into the string, such as a typed name, lets the user change the statement itself. That is how SQL injection works, and parameters close the door.

DECLARE @table sysname = N'Orders';
DECLARE @sql nvarchar(400) = N'SELECT @n = COUNT(*) FROM dbo.' + QUOTENAME(@table) + N';';
DECLARE @n int;
EXEC sys.sp_executesql @sql, N'@n int OUTPUT', @n = @n OUTPUT;
SELECT @n AS RowsInTable;

When the name comes from user input, check it against the catalog first. OBJECT_ID returns NULL for a name that doesn’t exist, and the procedure can stop there.

JSON and XML Results

A statement can return JSON or XML as one value. Declare the output as nvarchar(max) for JSON, or xml for XML. The inner SELECT uses FOR JSON PATH here.

DECLARE @sql nvarchar(400) = N'SELECT @j = (SELECT DepartmentID, DepartmentName FROM dbo.Departments WHERE DepartmentID <= 2 FOR JSON PATH);';
DECLARE @j nvarchar(max);
EXEC sys.sp_executesql @sql, N'@j nvarchar(max) OUTPUT', @j = @j OUTPUT;
SELECT @j AS DepartmentsJson;

New Identity Values

A dynamic INSERT runs in its own scope. SCOPE_IDENTITY() outside it can’t see the new row. In this demo it returns NULL, because no other insert with an identity column has run in the session. In a longer session, it returns the value of an older insert, which is worse. The @@IDENTITY function sees the row. It also counts rows that a trigger inserts, so it can return the wrong value. Read SCOPE_IDENTITY() inside the dynamic statement and return it through an OUTPUT parameter.

DECLARE @sql nvarchar(400) = N'INSERT INTO dbo.Tickets (Title) VALUES (N''Printer is down''); SELECT @id = SCOPE_IDENTITY();';
DECLARE @id int;
EXEC sys.sp_executesql @sql, N'@id int OUTPUT', @id = @id OUTPUT;
SELECT @id AS NewTicketViaOutput, SCOPE_IDENTITY() AS ScopeIdentityOutside, @@IDENTITY AS AtAtIdentity;

When You Need the Rows

Sometimes the result is a set of rows, not one value. Create a temporary table that matches the columns, and let INSERT take the output of sp_executesql.

DECLARE @sql nvarchar(400) = N'SELECT OrderID, Total FROM dbo.Orders WHERE DepartmentID = @ID ORDER BY OrderID;';
DROP TABLE IF EXISTS #Found;
CREATE TABLE #Found (OrderID int NOT NULL, Total decimal(10,2) NOT NULL);
INSERT INTO #Found (OrderID, Total) EXEC sys.sp_executesql @sql, N'@ID int', @ID = 4;
SELECT OrderID, Total FROM #Found;

You could argue that dynamic SQL is a last resort. When only a value changes, a static query with a parameter is simpler and safer. The optimizer reuses its plan. Reach for dynamic SQL when a name or the shape of the statement changes.

What to Remember

To return a dynamic SQL result, declare the statement as nvarchar and mark the parameter OUTPUT in both places. Assign the value inside the statement. Return one row, or aggregate. Wrap names in QUOTENAME and pass values as parameters.

When you finish with the demo, run the cleanup script.

USE master;
GO
IF DB_ID(N'DynamicResultDemo') IS NOT NULL
BEGIN
    ALTER DATABASE DynamicResultDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DynamicResultDemo;
END;

A dynamic SQL result is not returned, it is handed back through a parameter.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.