cannot transfer value from a variable to another variable

Question

I have a problem when I would like to transfer the value of a variable to another variable.

declare @column varchar(255);
set @column = 'cheesecake';

declare @tmp varchar(255);
set @tmp = (select @column from TEST where id = 1);

But in this case @tmp won't have the value of the table, but the name of the @column variable. I tried it with dynamic sql, but I got a syntax error.

declare @column varchar(255);
set @column = 'cheesecake';

declare @tmp varchar(255);
set @tmp = exec('select ' + @column + ' from TEST where id = 1');

How can I solve that the @tmp variable would contain the value of the query?

possible duplicate of [Getting result of dynamic SQL into a variable](http://stackoverflow.com/questions/3840730/getting-result-of-dynamic-sql-into-a-variable) — flup, Jul 30 '14 at 11:33

score 1 · Answer 1 · answered Jul 30 '14 at 11:46

EXEC executes in a different context, therefore the variables cannot be shared without hassle. You specify an output parameter to the statement using sp_executesql

DECLARE 
  @language nvarchar(255),
  @translation nvarchar(255), 
  @statement nvarchar(255)
SET @language = N'norwegian'
SET @statement = 'select @translation='+@language+' from Translations where id = 1'
EXEC sp_executesql 
  @statement, 
  N'@translation nvarchar(255) OUTPUT', 
  @translation OUTPUT
SELECT @translation

SQLFiddle

This seems to be the better way. OP should ideally accept this one, if he bothers to check back. — shree.pat18, Jul 30 '14 at 12:18

score -1 · Accepted Answer · edited May 23 '17 at 12:11

AFAIK, it is not possible to directly assign to a variable using `exec. A workaround to your issue would be to create a table variable to store the results of the dynamic query, and then set the value of the second variable using the table variable, like so:

declare @column varchar(255);
set @column = 'cheesecake';

declare @tmp varchar(255); 
declare @query nvarchar(255) = N'select ' + @column + N' from TEST where id = 1'

declare @tbl table(tmp varchar(255)) --Intermediate Table variable

insert into @tbl --Insert dynamic query results here
exec sp_executesql @query

select top 1 @tmp = tmp from @tbl --Assign value here

select @tmp

EDIT: I stand corrected. This answer shows how you can assign from dynamic SQL result to a variable by making use of OUTPUT parameters within the dynamic query.

cannot transfer value from a variable to another variable

2 Answers2