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 — flup
– flup, Commented Jul 30, 2014 at 11:33

flup · Accepted Answer · 2014-07-30 11:46:04Z

1

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

answered Jul 30, 2014 at 11:46

flup

27.2k8 gold badges56 silver badges75 bronze badges

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

1 Comment

shree.pat18 Over a year ago

This seems to be the better way. OP should ideally accept this one, if he bothers to check back.

Community · Accepted Answer · 2017-05-23 12:11:39Z

-1

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.

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Jul 30, 2014 at 7:48

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

Collectives™ on Stack Overflow

cannot transfer value from a variable to another variable

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related