Hello I would like to know how can e assign the value of a column to a variable in sql server 2012
For example consider the below scenario in which I retrieve the record id and emails (multiple emails as , seperated single row) for a list of persons mentioned in a multi person field in a form
The spd looks like following (contains many joins, but the important part is below)
-------------
------------
LEFT OUTER JOIN
(SELECT distinct, formid , STUFF((Select ',' + a.email from Db1.User b, Db1.formfields c
DB1.Userdetails a, DB1.FormRecordmapping d
where
where b.UserAddressid= a.id
and d.formfieldid= c.id
and b.Userid= d.FormValue
and c.id='3000'
and d.formid = ep.id
FOR XML PATH (''),TYPE).value('text()[1]','nvarchar(max)'),1,1,'') AS OPEMAIL
FROM [DB1.FORMRecordmapping ep) OPS
on OPS.formid= formid
Here the OPS.OPEMAIL value contains concatenated list of emails for example:
abc@gmail.com,xyz@hotmail.com,yrs@yahoo.com
I would like to have this concatenated list of emails in OPS.OPEMAIL assigned/ extracted to a string. These values are a result of the STUFF function shown above , but I am not able to assign this value to a string. For example in the above code if I use
--------
--------
LEFT OUTER JOIN
(SELECT distinct, formid , @Stemail =STUFF((Select ',' + a.email from Db1.User b, Db1.formfields c
DB1.Userdetails a, DB1.FormRecordmapping d
--------
--------
It shows an error- probably because this block is inside the LEFT OUTER JOIN. But I cannot remove the Left Outer join as it is required for joining with other values in the spd.
How can I achieve assigning the value (concatenated list of emails) returned by the stuff function in the above code to a string variable without modifying too much of above code?.
Any help would be appreciated.
Thanks
What I have tried:
Tried assigning a variable before the STUFF function in the select statement but showing error