sqlserver存储过程sp_send_dbmail邮件（html）实际应用-阿里云开发者社区

sqlserver存储过程sp_send_dbmail邮件（html）实际应用

2018-01-25 1688

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS SQL Server Serverless，2-4RCU 50GB 3个月

云数据库 RDS SQL Server，基础系列 2核4GB

简介： 前段时间因工作需求，特地学习了下sp_send_dbmail的使用，发现网上的示例对我这样的菜鸟太不友好/(ㄒoㄒ)/~~，好不容易完工来和大家分享一下，不谈理论，只管实践！如下是实际需求：-- =============================================-- T...

前段时间因工作需求，特地学习了下sp_send_dbmail的使用，发现网上的示例对我这样的菜鸟太不友好/(ㄒoㄒ)/~~，好不容易完工来和大家分享一下，不谈理论，只管实践！

如下是实际需求：

-- =============================================
-- Title: 集团资质一览表
-- Description1:<1、距离到期日期1年内和已过期的发到期提醒>
-- Description2:<2、表头【非附件】：公司名称、发证部门、证书名称、类别、等级、到期日期、预警级别>
-- Description3:<3、预警级别：假设距离到期日期月数为N。一级：N<=3；二级：3<N<=6；三级：N>=6>
-- Description4:<4、提醒人员：邮件提醒
-- =============================================

在这里sp_send_dbmail的参数不去做详述（我也不懂~），实际过程中我们需要用到的并不多，只需下面几行就能发送html格式的邮件了

Exec dbo.sp_send_dbmail 
@profile_name='crm***', --发件人姓名 
@recipients='156240***@qq.com', --邮箱（多个用;隔开）
@body=@tableHTML, --消息主体 
@body_format='HTML', --指定消息的格式，一般文本直接去掉即可，发送html格式的内容需加上
@subject ='资质到期预警'; -- 消息的主题

下面最主要的部分就是@tableHTML了，在这里我们使用两种方式去拼接html。

1.通过sql CAST 函数，网上的示例大多数是这种，愚笨的我不太看的懂，只能依瓢画葫。

declare @tableHTML varchar(max)
SET @tableHTML =
N'<H1 style="text-align:center">资质相关信息</H1>' +
N'<table border="1" cellpadding="3" cellspacing="0" align="center">' +
N'<tr><th width=100px" >公司名称</th>'+
N'<th width=250px>发证部门</th><th width=150px>证书名称</th>'+
N'<th width=50px>类别</th><th width=50px>等级</th>'+
N'<th width=60px>到期日期</th><th width=60px>预警级别</th></tr>'+
CAST ( (
select td = p.CompanyName, '',td = p.DeptName, '',td=p.Name,'', td = p.QualificationType, '',td = p.Level, '',td = p.ExpireDates, '',td=p.YJ,''
from(    
    select   
    CompanyName,DeptName,Name,QualificationType,Level,Convert(varchar(50),ExpireDate,111)ExpireDates,
    case when DATEDIFF(mm,getDate(),ExpireDate)<=3 then '一级预警' 
    when DATEDIFF(mm,getDate(),ExpireDate)<=6 then '二级预警' else '三级预警'end YJ
    from  T_Market***_JTZZ 
    where 12>=DATEDIFF(mm,getDate(),ExpireDate)
) p order by  p.ExpireDates  asc
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

Exec dbo.sp_send_dbmail 
    @profile_name='crm***', 
    @recipients = '156240***@qq.com', 
    @subject='资质到期预警', 
    @body=@tableHTML,
    @body_format = 'HTML' ;

2.通过游标动态绘制html，感觉这种更方便，虽然写起来有点啰嗦，但很灵活。

BEGIN
    declare @tableHTML varchar(max)
    declare @Companyname varchar(250) --公司名称
    declare @Deptname varchar(250)    --发证部门
    declare @Certname varchar(250)    --证书名称
    declare @Certtype varchar(50)     --证书类别
    declare @Certlevel varchar(50)    --证书等级
    declare @Expirdate varchar(20)    --到期时间
    declare @Warnlevel varchar(20)    --预警级别
    
    begin
        set @tableHTML = '<html><body><table><tr><td><p><font color="#000080" size="3" face="Verdana">您好！</font></p><p style="margin-left:30px;"><font size="3" face="Verdana">以下资质即将到期或已过期，请尽快办理资质延续：</font></p></td></tr>';
        --创建临时表#tbl_result
        create table #tbl_result(companyname varchar(250),deptname varchar(250),certname varchar(250),certtype varchar(50),certlevel varchar(50),expirdate varchar(20),warnlevel varchar(10));
        insert into #tbl_result 
        select CompanyName,DeptName,Name,QualificationType,Level,convert(varchar(20),ExpireDate,23) ExpireDate,case when ms<=3 then '一级' when ms>3 and ms<=6 then '二级' else '三级' end warnlevel
        from (
            select *,Datediff(MONTH,GETDATE(),ExpireDate) ms
            from T_Market***_JTZZ 
            where ExpireDate is not null and Datediff(MONTH,GETDATE(),ExpireDate)<=12
        ) res;
        
        declare @counts int;
        select @counts=count(*) from #tbl_result;
        --- 提醒列表
        if(@counts>0)
        begin
            set @tableHTML=@tableHTML+'<tr><td><table border="1" style="border:1px solid #d5d5d5;border-collapse:collapse;border-spacing:0;margin-left:30px;margin-top:20px;"><tr style="height:25px;background-color: rgb(219, 240, 251);"><th style="width:100px;">公司名称</th><th style="width:200px;">发证部门</th><th>证书名称</th><th style="width:60px;">类别</th><th style="width:80px;">等级</th><th style="width:100px;">到期日期</th><th style="width:80px;">预警级别</th></tr>';
            --申明游标
            Declare cur_cert Cursor for
            select companyname,deptname,certname,certtype,certlevel,expirdate,warnlevel from #tbl_result order by expirdate;
            --打开游标
            open cur_cert
            --循环并提取记录
            Fetch Next From cur_cert Into @Companyname,@Deptname,@Certname,@Certtype,@Certlevel,@Expirdate,@Warnlevel
            While (@@Fetch_Status=0)
            begin
                set @tableHTML = @tableHTML + '<tr><td align="center">'+@Companyname+'</td>';
                set @tableHTML = @tableHTML + '<td align="center">'+@Deptname+'</td>';
                set @tableHTML = @tableHTML + '<td align="center">'+@Certname+'</td>';
                set @tableHTML = @tableHTML + '<td align="center">'+@Certtype+'</td>';
                set @tableHTML = @tableHTML + '<td align="center">'+@Certlevel+'</td>';
                set @tableHTML = @tableHTML + '<td align="center">'+@Expirdate+'</td>';
                set @tableHTML = @tableHTML + '<td align="center">'+@Warnlevel+'</td></tr>';
                --继续遍历下一条记录
                Fetch Next From cur_cert Into @Companyname,@Deptname,@Certname,@Certtype,@Certlevel,@Expirdate,@Warnlevel
            end
            --关闭游标
            Close cur_cert
            --释放游标
            Deallocate cur_cert
            set @tableHTML = @tableHTML + '</table></td></tr>';
        end
        
        -- 发送邮件
            exec msdb.dbo.sp_send_dbmail 
            @profile_name='crm***',
            @recipients='156240***@qq.com',
            @body=@tableHTML,
            @body_format='HTML',
            @subject ='资质到期预警';
            
        -- 删除临时表(#tbl_result)
        if object_id('tempdb..#tbl_result') is not null 
        begin
            drop table #tbl_result;
        end
    end

END

看起来无疑第二中特别啰嗦，但个人感觉很好理解，游标拼接html部分思路很清楚，以上两个方式均经过实践，如需使用只需要将其中对应的字段、数据源替换掉即可，感谢诸位赏足，有什么不足之处还望大家见谅，本人菜鸟，无需鉴定~

sqlserver存储过程sp_send_dbmail邮件（html）实际应用

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

sqlserver存储过程sp_send_dbmail邮件（html）实际应用

热门文章

最新文章

相关课程

相关电子书

相关实验场景