Contact users using the email address(es) they provided?

We have a table called EmailList which has several columns, of which two are relevant to this post.

The column names are work_email and personal_email.

Users are required to provide their email addresses.

A user can provide one email address (work_email or personal_email) or both email addresses.

We have a stored procedure that queries the EmailList table to choose the email address(es) a user has provided and send the user an email with one or both email addresses.

The snippet below selects work_email as a list and stores the email addresses in the recipientList variable and then uses the recipientList to send emails to various recipients as blind copy.

        DECLARE @recipientList varchar(MAX)
        SET @recipientList = (STUFF((SELECT ';' + work_email
        FROM EmailList
        WHERE sent = 'No' FOR XML PATH('')),1,1,''))

        -- exec sp_send_cdontsmail @mail1, null,null,@content1,null
        EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'ReportMail', 
        @from_address = 'DoNotReply@domain.com',
        @recipients = 'someemail@domain.com', -- your email
        @blind_copy_recipients = @recipientList,
        @subject = 'Reminder',
        @body = @content1;

This seems to work.

Only issue we are having is that in the event that the user provides personal_email, not work email, how do we ensure that if work_email is null, then select personal_email?

I tried this elementary solution:

SET @recipientList = (STUFF((SELECT ';' + Coalesce(work_email, personal_email)
FROM EmailList
WHERE sent = 'No' FOR XML PATH('')),1,1,''))

Unfortunately, it did not work.

When I run the query, it does not give an error; however, it keeps showing blank instead of replacing the blank from work_email column with value from personal_email column.

Any ideas how to modify the script to first choose work_email but if work_email is not available, choose personal_email?

Sorry but I did not see anything related to my question from the SO suggested solutions.

Thank you in advance

>Solution :

If you have blank emails (rather than NULL) you may need to ignore those, too. One way to do so is using the NULLIF function. You supply it a value, and a value to compare to. If the two match it returns a NULL. Using that in combination with COALESCE will produce the first non-matching, not NULL value:

DECLARE @Users TABLE (UserID INT IDENTITY, UserName NVARCHAR(50), PersonalEmail NVARCHAR(200), WorkEmail NVARCHAR(200));
INSERT INTO @Users (UserName, PersonalEmail, WorkEmail) VALUES
('Joe', 'Joe@email.com', NULL), ('Bill', '', 'Bill@corp.org'), ('Jack', NULL, 'Jack@corp.net'), ('Sally', 'Sally@email.com', ''), ('Tairoc', 'Tairoc@email.com', 'Tairoc@corp.org');

SELECT *, COALESCE(NULLIF(PersonalEmail,''),WorkEmail) AS EmailToUse
  FROM @Users;