To add a local variable value to a dynamic query result set

Question

I have a dynamic query where in I want to add the value of a local variable to every row of the result set. I have a simplified version of the example below.

While this query works fine:

DECLARE @purchaseDate AS DATE
SET @purchaseDate = '12/23/2020'

SELECT Name, @purchaseDate 
FROM Fruits

The similar one in dynamic SQL does not work:

DECLARE @query AS NVARCHAR(MAX),
        @purchaseDate AS DATE

SET @purchaseDate = '12/23/2020'

SET @query = 'SELECT Name, @purchaseDate FROM Fruits' 

EXEC sp_executesql @query

I get the error

Must declare the scalar variable "@purchaseDate".

So I assumed I might need to declare my purchaseDate inside the query as the dynamic SQL query cannot access the variable. So I tried this:

DECLARE @query AS NVARCHAR(MAX)

SET @query = 'DECLARE @purchaseDate AS DATE' +
             'SET @purchaseDate = ' + '12/23/2020 ' +
             'SELECT Name, @purchaseDate FROM Fruits' 

EXEC  sp_executesql @query

But I get the same error message.

How do I go about fixing it?

Thom A · Accepted Answer · 2019-03-04T17:12:04.667

6

You can't reference a variable declared outside of a dynamic statement inside a dynamic statement. You need to add the declaration in the parameters and pass the value:

DECLARE @query  AS nvarchar(MAX),
        @purchaseDate AS date;
SET @purchaseDate = '20201223';
SET @query = 'SELECT Name,@purchaseDate FROM Fruits;'; 
EXEC sp_executesql @query, N'@purchaseDate date', @purchaseDate = @purchaseDate;

edited Mar 04 '19 at 17:12

answered Mar 04 '19 at 17:06

Thom A

88,727
11
45
75

Alex Gruebel · Answer 2 · 2019-03-04T17:26:54.137

The recommended way to put a variable in dynamic query with a prepared statement:

if OBJECT_ID('Fruits') is not null drop table Fruits

create table Fruits(
    name varchar(100)
)

insert into Fruits
values('apple')

DECLARE @purchaseDate AS DATE
SET @purchaseDate = '12/23/2020'

DECLARE @P1 int;  
EXEC sp_prepare @P1 output,   
    N'@purchaseDate date',  
    N'SELECT Name, @purchaseDate FROM Fruits';  
EXEC sp_execute @P1, @purchaseDate;  
EXEC sp_unprepare @P1;

Take a look at the Microsoft Doku.

If you are lazy or just want a simple adhoc solution (not recommended):

if OBJECT_ID('Fruits') is not null drop table Fruits

create table Fruits(
    name varchar(100)
)

insert into Fruits
values('apple')

DECLARE @query  AS NVARCHAR(MAX),
@purchaseDate AS DATE
SET @purchaseDate = '12/23/2020'
set @query = 'SELECT Name, ' + convert(nvarchar(10),@purchaseDate, 12) + ' FROM Fruits' 
exec sp_executesql @query

This is the worst way to pass a value to a dynamic statement. `@purchaseDate` is fortunately a `date` here but for a variable that is a `varchar`, `nvarchar`, etc, this would be a huge security flaw. — Thom A, Mar 04 '19 at 17:07

To add a local variable value to a dynamic query result set

2 Answers2