sqlserver存储过程sp_send_dbmail邮件(html)实际应用

2018-01-25 19:10:42来源:cnblogs.com作者:那家伙真懒人点击

分享

 前段时间因工作需求,特地学习了下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>资质相关信息</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  ascFOR 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><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"><tr><th>公司名称</th><th>发证部门</th><th>证书名称</th><th>类别</th><th>等级</th><th>到期日期</th><th>预警级别</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    endEND

 

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

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台