CODEWITHSUNDEEP
sqlsql-servervariablesdynamic
Kay
Kay

Reputation: 195

Assign results of dynamic sql into a variable

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!

Upvotes: 0

Views: 90

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

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
                             )

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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.

Upvotes: 0

Related Questions