sqlserver存儲過程sp_send_dbmail郵件（html）實際應用

-Advertisement-

前段時間因工作需求，特地學習了下sp_send_dbmail的使用，發現網上的示例對我這樣的菜鳥太不友好/(ㄒoㄒ)/~~，好不容易完工來和大家分享一下，不談理論，只管實踐！如下是實際需求： -- Title: 集團資質一覽表-- Description1:<1、距離到期日期1年內和已過期的發到期 ...

前段時間因工作需求，特地學習了下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部分思路很清楚，以上兩個方式均經過實踐，如需使用只需要將其中對應的欄位、數據源替換掉即可，感謝諸位賞足，有什麼不足之處還望大家見諒，本人菜鳥，無需鑒定~

您的分享是我們最大的動力!

-Advertisement-

更多相關文章

mysql索引的註意事項

索引的優點大大加快數據的查詢速度使用分組和排序進行數據查詢時，可以顯著減少查詢時分組和排序的時間創建唯一索引，能夠保證資料庫表中每一行數據的唯一性在實現數據的參考完整性方面，可以加速表和表之間的連接索引的缺點創建索引和維護索引需要消耗時間，並且隨著數據量的增加，時間也會增加索引需要占據 ...
vim撤銷undo與反撤銷redo

普通模式下 u 撤銷 ctrl + r 反撤銷 ...
Apache Sentry部署

三台hadoop集群，分別是master、slave1和slave2。下麵是這三台機器的軟體分佈： master：NameNode、ZK、HiveMetaSotre、HiveServer2、SentryServer slave1：DataNode、ZK slave2：DataNode、ZK 2 軟體 ...
Hive_UDF函數中集合對象初始化的註意事項

UDF函數中定義的集合對象何時初始化 udf函數放在sql中對某個欄位進行處理，那麼在底層會創建一個該類的對象，這個對象不斷的去調用這個evaluate(...)方法，截圖如下： 1.1 如果說對於每一條傳入UDF中需要處理的數據都需要全新的集合對象，那麼這個時候集合對象就需要在類中聲明，在eval ...
SQL Server（MySql）中的聯合主鍵（聯合索引）索引分析

聯合索引概念：當系統中某幾個欄位經常要做查詢，並且數據量較大，達到百萬級別，可多個欄位建成索引使用規則： 1.最左原則，根據索引欄位，由左往右依次and（where欄位很重要，從左往右） 2.Or 不會使用聯合索引 3.where語句中查詢欄位包含全部索引欄位，欄位順序無關，可隨意先後... ...
BugFree後臺統計Bug信息

以下為二維表信息 //統計嚴重等級Bug SELECT severity,count(severity) FROM `bf_bugview` where product_id=476 GROUP BY severity //統計創建者Bug SELECT created_by_name,count( ...
oracle中RAW數據類型

近日在研究v$latch視圖時，發現一個從未見過的數據類型。v$latch 中ADDR屬性的數據類型為RAW(4|8) 同時也發現v$process中的ADDR屬性的數據類型也為RAW(4|8)。於是查了一下oracle 的SQL Language Reference文檔，文檔如下描述： The R ...
MySQL官方文檔閱讀與記錄

接觸變成時間不久，之前對於MySQL的瞭解局限於簡單的CURD，沒有系統和深入的學習過，最近想要更深入的學習和瞭解一下MySQL，打算先從官方文檔入手。最新官方文檔：https://dev.mysql.com/doc/refman/8.0/en 1. MySQL對標準SQL的擴展 (1) MySQ ...