How to send multiple emails from sql server databa

2019-08-05 18:56发布

站内问答 / SQL-Server
1584 2 5

I have procedure which results a data table contains 3 columns Emails,proposal_Type,count(MatchestoEmail) the matches is based on proposal_type. Now I have to send a notification mails to the 'Emails' stating that they have these many number of matches based on Proposal_Type. the procedure output data will be like this.

Emails           Prop_Type   Matches

abc@gmail.com     1            3 

abc@gmail.com     2            4

def@gmail.com     3            2

I want the mails to be recipient and the remaining two columns to in the Body of the Email with some additional text. plz help me.

Thanks

2条回答
霸刀☆藐视天下
2楼-- · 2019-08-05 19:29

A cursor would solve your problem:

DECLARE <yourvariables>
DECLARE emailCursor CURSOR FOR
SELECT emails, prop_type, matches FROM <yourtable>
OPEN emailCursor
FETCH NEXT FROM emailCursor INTO @email, @prop_type, @matches
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @BODY = '<body text>' + @prop_type + '<more text>' + @matches
   EXEC msdb.dbo.sp_send_dbmail
   @recipients = @email,
   @subject = '<subject>',
   @body = @BODY
   FETCH NEXT FROM emailCursor INTO @email, @prop_type, @matches
END
CLOSE emailCursor
DEALLOCATE emailCursor
查看更多
0人赞 添加讨论(0) 举报
\"骚年 ilove
3楼-- · 2019-08-05 19:52

Edited

This should work

create proc [dbo].[SendProposalReport] as   
declare rscursor cursor read_only
for 
select Emails, Prop_Type, count(Matches) as Matches  from proposals
group by Emails, Prop_Type

    declare @Emails            nvarchar (100)
    declare @Prop_Type    int
    declare @Maches    int

open rscursor
fetch next from rscursor into @Emails,@Prop_Type,@Maches
while @@fetch_status=0
    begin

         EXEC msdb.dbo.sp_send_dbmail
        @recipients = @Emails,
        @subject = 'Example Email',
        @body = 'write your message here',
        @profile_name = 'ExampleProfile',

        @attach_query_result_as_file = 1        

    fetch next from rscursor into @Emails,@Prop_Type,@Maches
end
close rscursor
deallocate rscursor
查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(5)