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 = '[email protected]',
        @recipients = '[email protected]', -- 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