Assign results of dynamic sql into a variable

Question

What I want to do is to get the “value result” of the query in a variable @statement.

the code looks as follows:

declare @ID int, @name varchar (200), @statement varchar (200)

set @ID = 25
set @name = 'select FirstName + '' '' + LastName as ''Full Name''
             from person.person  
             where BusinessEntityID = ' + cast (@ID as varchar)'
set @statement = 'My name is @name'

I'd like to show the results from @statement, and I know i should use sp_executesql to execute my @name query, and output it @statement, but I am not sure how to do that.

Any thoughts and thanks!

John Cappelletti · Accepted Answer · 2020-03-18 17:34:20Z

3

A little confused. No need for the dynamic SQL

Example

declare @ID int, @name varchar (200)
set @id=25

set @name = 'My name is ' + (select FirstName + ' ' + LastName 
                               from person.person  
                               where BusinessEntityID = @ID
                             )

edited Mar 18, 2020 at 17:34

answered Mar 18, 2020 at 17:18

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

2 Comments

Kay Over a year ago

Thanks, but I'd like the output to be a statement as 'My name is xxxx', that's why I am considering the 3rd variable @statement...

John Cappelletti Over a year ago

@Kay Updated :)

Yogesh Sharma · Accepted Answer · 2020-03-18 17:30:16Z

0

You can use concat() :

select top (1) @name = concat('My name is ', FirstName, ' ', LastName)
from person.person  
where BusinessEntityID = @ID;

I would use to top (1) if BusinessEntityID matching pair has multi row.

answered Mar 18, 2020 at 17:30

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

Assign results of dynamic sql into a variable

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related