Click here to Skip to main content
14,981,768 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello

I have a query in an spd that is meant to concatenate strings (email addresses) into a single row separated by commas. I am using STUFF function for this. This is specific to SQL SERVER 2012
The query looks like this:

DECLARE @Email VARCHAR (200)

SELECT @Email = 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.Recordid= xxxx
FOR XML PATH (''),TYPE).value('text()[1]','nvarchar(max)'),1,1,'')
******************************************
Sample Out put looks like:
-------------------------------
abc@gmail.com,zyz@hotmail.com,pqr@yahoo.com
-----------------------------------
Basically this query fetches the email addresses of users from the user details table for the entries selected or added for a multi person field in the front end during the creation of the form, for a particular form (for a particular form ID) depending on how many number of persons are selected in the multi person field in the form.

The persons are already available in the system and each persons have details such as name , address, email etc stored in the database.

Now it works fine when the persons are selected in the multi person field in the form. But when no persons are selected for the multi person field ,then the query fails and the variable @Email does not return any value. (When I print the @Email variable in this case, I can see that there are no values for this case)

My requirement is that, I need to assign a default email address to the variable @Email when the above query does not satisfy the conditions or does not return any values.
I want the variable @Email to have a default value(email address string) when the above query does not return any values or it fails
How can I achieve this in the same query which I mentioned above?. I tried using Coalesce in the above query like this:

SELECT @Email = STUFF((Select ',' +COALESCE (a.email, 'defaultemail@xyz.com') 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.Recordid= xxxx
FOR XML PATH (''),TYPE).value('text()[1]','nvarchar(max)'),1,1,'')

but when I print the @Email variable after the query is run it did not print the default email and still showed as blank .

Can anybody help what I need to change in this query to achieve this?

What I have tried:

Tried using Coalesce but did not work.
Posted
Updated 28-May-21 3:50am

1 solution

I think you might be overthinking this.

After your existing SELECT statement, just do something like this:
SQL
SELECT @Email = COALESCE(@Email, 'defaultemail@xyz.com')


You need to make it a separate step.

Also, think about how many emails there may be max because right now you have @Email at only 200.
   
Comments
John George 2021 28-May-21 10:46am
   
@SeanChupas : Thank you. You were right I was overthinking. It worked. But is there a way to use the same statement which you sent, to work in my query rather than doing this as a separate step?
Thanks
SeanChupas 28-May-21 11:03am
   
No, because the way COALESCE works with STUFF, they work on each row. So you need something to happen after all that is done.

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