How to assign a column to a variable in Dynamic SQL

Question

I created the following dynamic SQL:

DECLARE @DATE    nvarchar(4) = '0824';
DECLARE @LocalID INT;

SET @sql = N'SELECT Col1, Col2, Col3, Col4, ';

SET @sql = @sql + N'@LocalID = Col5 FROM Table WHERE AdminDate = @InternalDate ';

EXEC sp_executesql @sql, N'@InternalDate nvarchar(4), @InternalLocalID INT OUTPUT', 
     @InternalDate = @Date, @InternalLocalID = @LocalID OUTPUT;

SELECT @LocalID AS LocalID;

When I executed it, I get the error message:

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations

I believe the error was caused by multiple columns in the SELECT statement. How can I assign one of the columns in the SELECT statement to a variable (in this case to get the value of Col5)?

When you use the Exec method, you get a new execution context where the @LocalID variable doesn't exist, so you can't include in the SQL string unless it's also included in the parameter list for sq_exectuesql. — Joel Coehoorn
– Joel Coehoorn, Commented Sep 9, 2024 at 16:49
The way to do this is to put data into a temp table and then do what you need in two steps — siggemannen
– siggemannen, Commented Sep 9, 2024 at 18:03
TL;DR: You can't assign a variable in the same data set you return data in, dynamic SQL or not (and your SQL isn't actually dynamic). — Thom A
– Thom A ♦, Commented Sep 9, 2024 at 18:38
Why would you need to do this, why not just return a columns in the same resultset? And why does this need to be dynamic SQL? — Charlieface
– Charlieface, Commented Sep 9, 2024 at 20:44

user25043454 · Accepted Answer · 2024-09-09 17:06:10Z

0

Not sure why you introduced a different name for the variable. If you keep the right name:

DECLARE @sql nvarchar(max);
DECLARE @LocalID   INT;

SET @sql = N'SELECT @LocalID = 7;';

EXEC sp_executesql @sql, N'@LocalID INT OUTPUT', 
     @LocalID = @LocalID OUTPUT;

SELECT @LocalID as LocalID;

Example db<>fiddle

If you really want to use a different name inside the dynamic SQL, you have to use it in multiple places:

DECLARE @sql nvarchar(max);
DECLARE @LocalID   INT;

SET @sql = N'SELECT @InternalLocalID = 7;';

EXEC sp_executesql @sql, N'@InternalLocalID INT OUTPUT', 
     @InternalLocalID = @LocalID OUTPUT;

SELECT @LocalID as LocalID;

answered Sep 9, 2024 at 17:06

user25043454

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to assign a column to a variable in Dynamic SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related