How do you send emails to multiple people, with different criteria, using dbmail in MS SQL

Question

I have the below SQL query I am building to send automated emails to various people. Right now I have it setup to email a table to one person.

What I would like to do is send specific query results to specific people based on a condition. For example I want to send an email containing only data where SC = 20 to abc@email.com, and a separate email where sc = 30 to xyz@email.com.

In addition, if there is no data for that particular SC then I would not send the email to them.

How can this be adjusted to accommodate that? I'd appreciate any feedback. Thank you.

use DST14000BUSD

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Data Issues</H1>' +
    N'<p>This is an automated email. Please review and correct these issues as soon as possible. Thank you.</p>' +    
    N'<p>Periods in address or city field. Periods are not allowed in addresses because they are not supported by other systems.</p>' +  
    N'<table border="1">' +
    N'<tr><th>Issue</th><th>Status</th>' +
    N'<th>School</th><th>ID</th><th>Student #</th>' +
    N'<th>First Name</th><th>Last Name</th><th>Address</th><th>City</th><th>Residence Address</th><th>Residence City</th></tr>' +
    CAST ( ( SELECT td = 'Period in address or city field',       '',
                    td = TG, '', td = sc, '',
                    td = id, '', td = sn, '', td = fn, '', td = ln, '',
                    td = ad, '', td = cy, '', td = rad, '',
                    td = rcy
from stu
where ((AD like '%.%' or RAD like '%.%' or CY like '%.%' or RCY like '%.%')
or (AD like '%!%' or RAD like '%!%' or CY like '%!%' or RCY like '%!%'))
and DEL = 0
order by SC              

              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' +

N'<p>Email processed on ' + CONVERT(VARCHAR(10),GETDATE(),101) + '</p>' ;    

EXEC msdb.dbo.sp_send_dbmail @recipients='email@email.com',
    @from_address = 'email@email.com',
    @subject = 'Data Issues',
    @body = @tableHTML,
    @body_format = 'HTML' ;

Answer 1

You can use a CURSOR to loop through your recipients.

DECLARE @tableHTML NVARCHAR(MAX),
        @Email NVARCHAR(255),
        @Code INT

-- test mapping table 
CREATE TABLE #map ( Email nvarchar(255), Code int   )
insert #map values ('abc@gmail.com', 20)
insert #map values  ('xyz@email.com', 30)

-- CURSOR to iterate through rows in #map
DECLARE mail_cursor CURSOR
FOR SELECT email, code FROM #map
OPEN mail_cursor        

-- Fetch the first row
FETCH NEXT FROM mail_cursor
INTO @Email, @Code

-- Check for successful fetch
WHILE (@@FETCH_STATUS = 0)
BEGIN

    -- Only proceed if there is data to send
    IF (EXISTS (select 1 FROM stu WHERE sc = @Code))
    BEGIN

        -- Build email body. Same as you have, but limit to sc=@code
        SET @tableHTML = N'..'

        -- send to #map.Email
        EXEC msdb.dbo.sp_send_dbmail @recipients=@Email,
            @from_address = 'email@email.com',
            @subject = 'Data Issues',
            @body = @tableHTML,
            @body_format = 'HTML' ;
    END

    -- Fetch the next row
    FETCH NEXT FROM mail_cursor
    INTO @Email, @Code
END

-- Cleanup
CLOSE mail_cursor
DEALLOCATE mail_cursor
DROP TABLE #map