sql server - How do you send emails to multiple people, with different criteria, using dbmail in MS SQL -
i have below sql query building send automated emails various people. right have setup email table 1 person.
what send specific query results specific people based on condition. example want send email containing data sc = 20 abc@email.com, , separate email sc = 30 xyz@email.com.
in addition, if there no data particular sc not send email them.
how can adjusted accommodate that? i'd appreciate feedback. thank you.
use dst14000busd declare @tablehtml nvarchar(max) ; set @tablehtml = n'<h1>data issues</h1>' + n'<p>this automated email. please review , correct these issues possible. thank you.</p>' + n'<p>periods in address or city field. periods not allowed in addresses because not supported 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 stu ((ad '%.%' or rad '%.%' or cy '%.%' or rcy '%.%') or (ad '%!%' or rad '%!%' or cy '%!%' or rcy '%!%')) , del = 0 order sc xml path('tr'), type ) 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' ;
you can use cursor loop through 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 iterate through rows in #map declare mail_cursor cursor select email, code #map open mail_cursor -- fetch first row fetch next mail_cursor @email, @code -- check successful fetch while (@@fetch_status = 0) begin -- proceed if there data send if (exists (select 1 stu sc = @code)) begin -- build email body. same have, limit sc=@code set @tablehtml = n'..' -- send #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 next row fetch next mail_cursor @email, @code end -- cleanup close mail_cursor deallocate mail_cursor drop table #map