sql - Use Variable value in a SELECT statement -

i have declared variable @s , storing sub query value in returning more 1 record , want use in select statement , can possible,

here query trying getting error must declare scalar variable "@s"

declare @s nvarchar(max)     set @s = '(select es.firstname [user] es   es.userid in (select customeruserid orderinfo))'  select           orderinfoid,         borrowerfirstname consumerfirstname,         borrowerlastname consumerlastname,         requestedurl,         requests,         select @s,         u.firstname +'' ''+ u.lastname affiliate,         o.requestdatetime dateoftransaction,         o.requestipaddress originatingipaddress,          o.requests status orderinfo o  inner join [user] u on o.affiliateid = u.userid 

is possible that. appreciated

if want append as single string result of sub-query every row produced main query, then, first of have initialize @s:

declare @s nvarchar(max) = '' 

then set it:

select @s = @s + ' ' + es.firstname  [user] es    es.userid in (select customeruserid orderinfo) 

and consume contents in main query:

select           orderinfoid,         borrowerfirstname consumerfirstname,         borrowerlastname consumerlastname,         requestedurl,         requests,         (select @s),         u.firstname +'' ''+ u.lastname affiliate,         o.requestdatetime dateoftransaction,         o.requestipaddress originatingipaddress,          o.requests status orderinfo o  inner join [user] u on o.affiliateid = u.userid