sqlserver存储过程sp_send_dbmail邮件（html）实际应用
前段时间因工作需求，特地学习了下sp_send_dbmail的使用，发现网上的示例对我这样的菜鸟太不友好/(ㄒoㄒ)/~~，好不容易完工来和大家分享一下，不谈理论，只管实践！
如下是实际需求：
-- =============================================
-- Title: 集团资质一览表
-- Description1:<1、距离到期日期1年内和已过期的发到期提醒>
-- Description2:<2、表头【非附件】：公司名称、发证部门、证书名称、类别、等级、到期日期、预警级别>
-- Description3:<3、预警级别：假设距离到期日期月数为N。一级：N<=3；二级：3
-- 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'资质相关信息
' + N'' + N'
' ; Exec dbo.sp_send_dbmail @profile_name='crm***', @recipients = '156240***@qq.com', @subject='资质到期预警', @body=@tableHTML, @body_format = 'HTML' ;'+ 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' 公司名称 '+ N'发证部门 证书名称 '+ N'类别 等级 '+ N'到期日期 预警级别
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 = '
'; --创建临时表#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+' 您好！
以下资质即将到期或已过期，请尽快办理资质延续：
'; 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
'; --申明游标 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 + ' 公司名称 发证部门 证书名称 类别 等级 到期日期 预警级别 '; --继续遍历下一条记录 Fetch Next From cur_cert Into @Companyname,@Deptname,@Certname,@Certtype,@Certlevel,@Expirdate,@Warnlevel end --关闭游标 Close cur_cert --释放游标 Deallocate cur_cert set @tableHTML = @tableHTML + ' '+@Companyname+' '; set @tableHTML = @tableHTML + ''+@Deptname+' '; set @tableHTML = @tableHTML + ''+@Certname+' '; set @tableHTML = @tableHTML + ''+@Certtype+' '; set @tableHTML = @tableHTML + ''+@Certlevel+' '; set @tableHTML = @tableHTML + ''+@Expirdate+' '; set @tableHTML = @tableHTML + ''+@Warnlevel+' 看起来无疑第二中特别啰嗦，但个人感觉很好理解，游标拼接html部分思路很清楚，以上两个方式均经过实践，如需使用只需要将其中对应的字段、数据源替换掉即可，感谢诸位赏足，有什么不足之处还望大家见谅，本人菜鸟，无需鉴定~
相关