February 8, 2013 at 11:48 pm
Hi Experts,
if I run one Query then I will get this result:
Assume,
select responsibleprovider,emailaddress from table1
responsibleprovideremailaddress
aaaa, bbbbbaaaar@uphs.upenn.edu
ddddd, Ppppddddp@uphs.upenn.edu
if I run another query I get this result:
Assume,
select Count ,responsibleprovider from tablereport
Count responsibleprovider
6aaaa, bbbbb
8ddddd, Pppp
I need to send this people email about the number of reports waiting for them (knowing their email address from the previous SQL):
For example, contact Email will get email: You have 1 report
aaaa, bbbbb will get email: You have 4 reports
I am new to Mail functionality in SQL Server ,
Could you please advice me , how to send an mail to repective preoviders with some text.
Thanks!
February 9, 2013 at 6:43 am
Assuming you already have Database Mail setup with a default profile in place you could do something like this. Just replace the use of the CTEs I created to bring in test data with references to the real tables where your data resides.
DECLARE @sql NVARCHAR(MAX) = N'';
WITH cte(responsibleprovider, emailaddress)
AS (
SELECT 'aaaa, bbbbb',
'aaaar@uphs.upenn.edu'
UNION ALL
SELECT 'ddddd, Pppp',
'ddddp@uphs.upenn.edu'
),
cte2(ReportCount, responsibleprovider)
AS (
SELECT 6,
'aaaa, bbbbb'
UNION ALL
SELECT 8,
'ddddd, Pppp'
)
SELECT @sql += 'EXEC msdb.dbo.sp_send_dbmail
@recipients = ' + QUOTENAME(emailaddress, '''') + ',
@subject = N''You have ' + CAST(reportcount AS NVARCHAR(11)) + ' report.'',
@body = N''Say something else here...''
'
FROM cte
JOIN cte2 ON cte.responsibleprovider = cte2.responsibleprovider;
PRINT @sql;
--EXEC(@sql);
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply