How can we extract the value from a stuff function variable for processing

Question

Hello I would like to know how can we 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 comma separated in single row) for a list of persons mentioned in a multi person field in a form The spd looks like following (contains many joins) A sample structure of the entire spd is as follows .

BEGIN
DECLARE @VAR1 VARCHAR(50)
DECLARE @VAR2 VARCHAR(50)
DECLARE @FINAL NVARCHAR(MAX)
DECLARE @DEFAULT VARCHAR(50)
SET @DEFAULTE = 'defautemail@xyz.com'

select  @var1 = isnull( email,@DEFAULTE) from usertable inner join userdet on 
                 usertable .recid = userdet.recid and usertable.uid=1 

SET @var2 = isnull (email,@DEFAULTE)  from usertable inner join userdet on 
            usertable .recid = userdet.recid and usertable.uid=1  
SET @FINAL= @var1 + "," +@var2


SELECT FORM.RECORD ID AS [RECORD ID],
       PRODUCT.DESCRIPTION AS [DESCRIPTION],
       SUP.NAME AS [SUPPLIER],
    --  @FINAL AS [FINALEMAILS]
       ABC AS [RESULTEMAILS]
     
     FROM DB1.FORM F INNER JOIN
          DB1.PROJTYPE P
          ON F.RECORDID= P.RECORDID
      LEFT OUTER JOIN
       (select Sup.formid,Supdet.name
       From DB1.Formdet fdet
       inner join DB1.Form Fo on Fo.recordid= Sup.formid)SupRec
       On RecSup.formid = form.recordid
       LEFT OUTER JOIN 
       ( Select FData.Formid,FDDesc.description 
       From DB1.FormDetails FData , DB1.FormDesc F
       on Fdata.formid= F.Formid) Desc on Desc.Formid= form.recordid
     
LEFT OUTER JOIN
(SELECT distinct formid , abc= 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,'') 
FROM [DB1.FORMRecordmapping ep) OPS
on OPS.formid= formid
WHERE  F.NOTIFREQ ='yes'
AND P= 300
END

Sample output shows as :

Record Id     Descripton  Supplier  ResultEmails
---------------------------------------------------------------------------------
22222          DescrTest   Sup1     abc@xyz.com,yrc@hotmail.com,h@xyz.com
33333          DescTest2   Sup2     abc@gmail.com
-------------------------------------------------------------

My requirement is: How can I achieve assigning the value (comma separated list of emails) returned by the stuff function in the above code to a string variable (varchar or nvarchar type) for further processing , without modifying too much of above code?. Any help would be appreciated. Thanks

You cannot embed a variable assignment within a derived table - such assignments are only allowed (generally speaking) in the select list. Presumably your query produces (or at least can produce) a multi-row resultset - so which row from that resultset do you expect to use as the source of your assignment? This sounds like a [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). You will need a very different approach but impossible to make suggestions without more details. — SMor, May 29 '21 at 00:25
And EVOLVE! No one should be using [old-style joins](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins). — SMor, May 29 '21 at 00:27
Hello SMor : Thank you for your reply. I need the value returned by the STUFF function in the above query. This valued returned by the stuff function , in this case is a concatenated list of (comma separated) emails for example in this format: abc@gmail.com,xyz@hotmail.com (Maximum 6 emails). In the above query I know that we can do like this:(SELECT distinct, formid , Stemail =STUFF((Select ',' + a.email from Db1.User b, Db1.formfields c,DB1.Userdetails a, DB1.FormRecordmapping d.... But this only displays the email list . I want to extract the value returned by the STUFF fn to a variable. — John George, May 29 '21 at 05:47
SMor: Background of this query :This is used to concatenate the email of persons that is selected in a multi person field of a form. This list is required for a service which connects to the spd to send notifications. In the query , I am joining the form, data field, user , userdet table to fetch the emails of the person selected in the multi person DF of the form and I use the STUFF function to create a concatenated list of the emails in order to be passed to the service. The spd also contains other joins to fetch other values to be passed to the service. let me know if u need more details. — John George, May 29 '21 at 06:33
Presuming you're expecting one record from your query, and you want the combined emails assigned to a variable. You could first insert your query result into a temp table. Then do a SELECT @YourVariable = ABC FROM YourTempTable. — joby-flick, May 29 '21 at 17:02
Why do you need it inside a variable? If it is for use in the same query, you can use `CROSS APPLY` to use it elsewhere in the query. If it is for use later, you will have to insert the whole resultset into a temp table. Or you can run just this part separately. We can't help unless we know what you are trying to do with this result — Charlieface, May 30 '21 at 00:21
@Charlieface : I need this value returned by the STUFF to be assigned to a variable in order to pass it to a service in order to send notifications . When the service runs this stored proc , it should pick up the value in the variable and send the notifications to the email addresses stored in the variable. The value returned by the stuff function will contain a comma separated list of email ids. (Please refer above to my first 2 comments I addressed to SMor . I have explained the purpose of why I use the STUFF function my query (spd) ) .pls explain how can i achieve this using temp table? — John George, May 30 '21 at 08:56
@Charlieface : In order to explain it to u further. I have a service which sends emails to a particular email addresses based on an event of a form. I am using an spd to extract data required by the service to send emails . the service when run connects to this spd to fetch up the eligible records for which it needs to send emails and also the various data required for body of the email and also the email ids for which it need to send the emails. The query which i posted initially is part of this spd and is used to fetch emails of persons selected in a multi person field of the form(contd...) — John George, May 30 '21 at 09:13
You may want to just pass all the rows to that service and let it concatenate them together, it's always more difficult doing that in SQL. Or, you could run this part as a separate query. — Charlieface, May 30 '21 at 09:24
@Charlieface : (contd ..) These emails I will need as a , separated string , to pass to the service for sending emails to these email ids. That it the reason I am using STUFF function here . If there are 3 persons selected in the multiperson DF of the form this query will fetch 3 email addr of persons selected as (,) separated string . Also note that I am using several other joins in the spd to select other data of the eligible records required by the service to send emails. So I cannot use stuff query as separate. I need to use it as part of other joins. Let me knw if u need more details. — John George, May 30 '21 at 09:27
@Charlieface I cannot use the STUFF part as a separate query because I am selecting other data from the form by joining several tables along with the emails for a particular eligible record based on some conditions. The email ids for the eligible records(forms) fetched will be stored in a string as a comma separate string value and it will be passed to the service. The goal of the service is to send the notification only and nothing else. It does not do concatenation. This part needs to be done in the spd itself. — John George, May 30 '21 at 09:34
@Charlieface : Ok Can you tell me how can I use a temp table here in my initial query?------ -------- LEFT OUTER JOIN (SELECT distinct, formid , **@Stemail =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,'') FROM [DB1.FORMRecordmapping ep) OPS on OPS.formid= formid — John George, May 30 '21 at 09:35
`CREATE TABLE #tmp (col1 ........); INSERT #tmp (col1.....) SELECT ....` you need to insert the whole outer query into the table — Charlieface, May 30 '21 at 09:38
@Charlieface Ok then how do you assign the concatenated emails from the temp table to a variable of varchar type ? Ultimately I need the concatenated emails in a variable and this variable will be used in the select statement of the complete sql query which contains the complete joins and all data field values that will be passed to the service — John George, May 30 '21 at 09:42
Going round in circles here: if you want it in a single variable then it must be a single row. If it's a single row, why can you not run it as a separate query? If it's not a single row how can you assign it to a variable? And what is not clear about "dump it all into a temp table and select back out what you need into a variable"? Ultimately you haven't shown us your full process so we have no way of advising you further — Charlieface, May 30 '21 at 09:47
@Charlieface Ok to be a little more clear : The whole stored proc (which contains the complete joins along with the stuff query I mentioned ) when run will produce an out put something similar to this (contd in below comment , as I cannot copy the entire text in this comment ...) — John George, May 30 '21 at 10:00
Record Id Name Short desc Descripton Supplier Storename Concatenated emails ------------------------------------------------------------------------------------------------ 22222 Test1 Test desc DescrTest Sup1 Store1 abc@xyz.com,yrc@hotmail.com,h@xyz.com 33333 Test2 Test desc2 DescTest2 Sup2 Store2 abc@gmail.com Here the 'concatenated emails ' part is the one which I am fetching using stuff using the initial query I posted. ( contd in next comment....) — John George, May 30 '21 at 10:03
I would require the concatenated emails in a variable because apart from the emails which I showed in my example there are a set of other emails for which also I need to concatenate with this list in order to pass to the service. For ex: in the above example for record Id: 2222 I have concatenated emails selected as: Store1 abc@xyz.com,yrc@hotmail.com,h@xyz.com . Apart from these emails I have a static set of other emails which I need to concatenate with this 3 emails in order to pass to the service. (contd in next..) — John George, May 30 '21 at 10:10
@Charlieface (contd...) I am using a seperate set of select queries with inner joins (independent query) to select this static set of emails in the same spd and assign to a variable something like this : SET var1 = select email from usertable inner join person on usertable .recid = person.recid and personid=1 SET var2 = select email from usertable inner join person on usertable .recid = person.recid and personid=2 SET Final = var1+','+var2 (contd..) — John George, May 30 '21 at 10:17
@Charlieface To this 'Final' variable which is already stored with a list of emails as I showed above (using independent select queries, independent from the final sql query used in the spd) I am concatenating the email strings I extracted using STUFF function and this 'FINAL' variable I will be using in the select statement of my final SQL query in the spd that picks up all the data field values for the form using several inner and outer joins. — John George, May 30 '21 at 10:22
@Charlieface : The problem is I cannot add lenghty comments due to the restriction in characters. That is y I am putting it in multiple comments as continuition .. please excuse me. I hope you got some idea of the entire process of the spd. — John George, May 30 '21 at 10:26
You seem to have lost the [edit] button for your post. Don't put all this in comments — Charlieface, May 30 '21 at 10:27
@Charlieface : I have edited my post to show a sample structure of spd. Hope you will be able to understand now. Can you please check , in the light of what we discussed last? — John George, May 30 '21 at 11:47

How can we extract the value from a stuff function variable for processing

0 Answers0