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

时间：2023-02-24 23:49:29

前段时间因工作需求，特地学习了下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

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

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

sqlserver存储过程sp_send_dbmail邮件（html）实际应用的更多相关文章

  1. SQLServer 存储过程&plus;定时任务发邮件

    SQLServer 代理发邮件需要开启SQL Server 代理服务器,然后,在[管理]-[数据库邮件]中,右键点击配置数据库邮件. 我用的是腾讯的企业邮箱,个人的163邮箱略微不同.下图是相关邮件的 ...

  2. JSON序列化及利用SqlServer系统存储过程sp&lowbar;send&lowbar;dbmail发送邮件（一）

    JSON序列化 http://www.cnblogs.com/yubaolee/p/json_serialize.html 利用SqlServer系统存储过程sp_send_dbmail发送邮件(一) ...

  3. 解剖SQLSERVER 第十五篇 SQLSERVER存储过程的源文本存放在哪里？（译）

    解剖SQLSERVER 第十五篇  SQLSERVER存储过程的源文本存放在哪里?(译) http://improve.dk/where-does-sql-server-store-the-sourc ...

  4. Sqlserver 存储过程中结合事务的代码

    Sqlserver 存储过程中结合事务的代码  --方式一 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ ...

  5. SqlServer存储过程学习笔记（增删改查）

    * IDENT_CURRENT 返回为任何会话和任何作用域中的特定表最后生成的标识值. CREATE PROCEDURE [dbo].[PR_NewsAffiche_AddNewsEntity] ( ...

  6. SQLServer 存储过程嵌套事务处理

    原文:SQLServer 存储过程嵌套事务处理 某个存储过程可能被单独调用,也可能由其他存储过程嵌套调用,则可能会发生嵌套事务的情形. 下面是一种解决存储过程嵌套调用的通用代码,在不能确定存储过程是否 ...

  7. 创建并在项目中调用SQLSERVER存储过程的简单示例

    使用SQLSERVER存储过程可以很大的提高程序运行速度,简化编程维护难度,现已得到广泛应用.创建存储过程 和数据表一样,在使用之前需要创建存储过程,它的简明语法是: 引用: Create PROC ...

  8. SQLSERVER存储过程语法详解

    CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ...

  9. SqlServer存储过程详解

    SqlServer存储过程详解 1.创建存储过程的基本语法模板: if (exists (select * from sys.objects where name = 'pro_name')) dro ...

随机推荐

  1. 【001：C&num; 中 get set 简写存在的陷阱】

    如下代码: public class Age { private int ageNum ; public int AgeNum { get{ return this.ageNum; } set{ th ...

  2. 【SVN】Error running context&colon; 由于目标计算机积极拒绝&comma;无法连接

    SVN服务没开启,步骤如下: 1.打开[控制面板]→[管理工具]→[服务]: 2.找到[visual SVN Sever],右击选择[启动]: 3.服务开启后,导入数据就成功了!

  3. HTTP学习（一）初识HTTP

    作为一名准前端开发工程师,必须要对http基础知识有一定的了解,可是想学习HTTP相关的知识,发现国内只有两本相关的图书,<HTTP权威指南>和<图解http>,所有的书但凡带 ...

  4. Pycharm远程调试服务器代码（使用Pipenv管理虚拟环境）

    准备工作 1.随便准备一个项目工程,在本地用Pipenv创建一个虚拟环境并生成Pipfile和pipfile.lock文件,如下: 2.准备一台服务器,我这里使用阿里云的ECS SSH连接上 $ ss ...

  5. 提高MySQL数据库的安全性

    1. 更改默认端口(默认3306) 可以从一定程度上防止端口扫描工具的扫描 2. 删除掉test数据库 drop database test; 3. 密码改的复杂些 # 1 set password ...

  6. django项目中在settings中配置静态文件

    STATICFILES_DIRS = [ os.path.join(BASE_DIR,'static'), ] 写成大写可能看不太懂,但是小写的意思非常明显:staticfiles_dir = [ o ...

  7. HDU 1392 Surround the Trees（凸包）题解

    题意:给一堆二维的点,问你最少用多少距离能把这些点都围起来 思路: 凸包: 我们先找到所有点中最左下角的点p1,这个点绝对在凸包上.接下来对剩余点按照相对p1的角度升序排序,角度一样按距离升序排序.因 ...

  8. day 90 DjangoRestFramework学习二之序列化组件

      DjangoRestFramework学习二之序列化组件   本节目录 一 序列化组件 二 xxx 三 xxx 四 xxx 五 xxx 六 xxx 七 xxx 八 xxx 一 序列化组件 首先按照 ...

  9. day6 网络 HTML模板

    1.HTML模板 HTML模板 baidu一下 http://www.cssmoban.com/ http://www.cnblogs.com/web-d/archive/2010/04/16/171 ...

  10. Loadrunner&lowbar;http长连接设置

    最近协助同事解决了几个问题,也对loadrunner的一些设置加深了理解,关键是更加知其所以然. ljonathan http://www.51testing.com/html/48/202848-2 ...