CODEWITHSUNDEEP
sql-serverdynamic-sql
Stephen
Stephen

Reputation: 1

How to assign a column to a variable in Dynamic SQL

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)?

Upvotes: 0

Views: 77

Answers (1)

user25043454
user25043454

Reputation:

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;

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;

Upvotes: 0

Related Questions