Click here to Skip to main content
14,971,029 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900