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