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

CodePudding user response：

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', '[email protected]', NULL), ('Bill', '', '[email protected]'), ('Jack', NULL, '[email protected]'), ('Sally', '[email protected]', ''), ('Tairoc', '[email protected]', '[email protected]');

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