如何使用sp_send_dbmail过程从sql server数据库发送多封电子邮件

我有一个过程，它导致一个数据表包含3列Emails、proposal_Type、count（MatchestoEmail），匹配基于proposal_Type。现在，我必须向"电子邮件"发送一封通知邮件，说明他们有这么多基于Proposal_Type的匹配数。过程输出数据将是这样的。

Emails           Prop_Type   Matches
abc@gmail.com     1            3 
abc@gmail.com     2            4
def@gmail.com     3            2

我希望邮件是收件人，剩下的两列在电子邮件正文中添加一些额外的文本。请帮帮我。

感谢

光标可以解决您的问题：

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

已编辑

这应该工作

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

相关内容

最新更新

热门标签：