SQL SERVER - 通用封装发送表格的邮件

场景：

当用户需要每天通过T-SQL检索时，需要把对应结果发邮件出去。正好用户有SQL SERVER ,可利用SQL SERVER 邮件功能进行本节省略如何配置发送邮件设置。直接讲解相关知识点

知识点

编号	内容	内容
1	select * from sys.dm_exec_describe_first_result_set (' exec t',null,0) t	此T-SQL 可以返回 列的名称，大小等信息。 例如存储过程 T 有三列，那 第一个函数  excec  t  就是对应T-SQL，通过DMV 表值函数， 可以获得列的情况  举例说明 create proc t as  begin select 1 as id, 2 as tel  end go select * from sys.dm_exec_describe_first_result_set (' exec t',null,0) t
2	EXEC msdb.dbo.sp_send_dbmail @profile_name='私人的', @recipients=@recipients, @subject=@subject, @body=@sqlbody, @body_format = 'HTML'	此存储过程是发送邮件存储过程 ，1. 第一参数"私人的”是我配置邮件服务器的个性设置名称，个性设置有发送人邮箱，显示名你等等信息。    2.@body_format = 'HTML' 即我们发送 HTML格式的代码    3.@subject=@subject 便是发送邮件主题   4.@recipients=@recipients, 收件人   5.  @body=@sqlbody, HTML内容   对于上面中@recipients，@sqlbody，@recipients 都是一会用到的变量。
3	<style> table { float:left; } table, th , td { border: 1px solid black; border-collapse: collapse; padding: 5px;	这些元素是CSS定义，就是对表格的样式定义。 左边CSS定义就是边框为1，实线，黑色，边框间合并， 间格 5PX
4	STRING_AGG	举例说明 select STRING_AGG(id,',') from ( select 'a' as id union all select 'b' ) ct   结果就是 a,b  此种写法解决以前要拼接 字符串的复杂，要用for xml , stuff， 并且难看。
5	+=	举例说明 declare @str nvarchar(max)='' select @str+=CONCAT(id,' salary: ',mount,';') from ( select 'a' as id,'2' as mount union all select 'b' ,'3') ct select @str   上面结果，会把结果集， 拼成一个大字符串，给 @str变量， 使用 += 可以把每一行，都进行拼接，有的小朋友会考虑 用游标也迭代每一行，其实不必这么麻烦用 +=就可以了。
6	exec sp_executesql @v_runsql, N'@v_content nvarchar(max) out', @v_content out	执行一个变量中的T-SQL，并把结果返回出来  举例说明 declare @v_runsql nvarchar(max)=' select @v_content=11+443 ', @v_content nvarchar(max) exec sp_executesql @v_runsql, N'@v_content nvarchar(max) out', @v_content out select @v_content    最后结果就是 454

思路

1.  创建临时表把结果集写到临时表中，临时表的结构信息 把列定义字符串类型，建议 nvarchar 防止其它语言字符。列信息可以通过sys.dm_exec_describe_first_result_set  获得

2. 创建一个html 模板，包括CSS定义，和 表各占位符以便替换真正内容。

3. 把标题拼接成 表格HTML，替换占位符

4.把表格内容，变成一个格式化的字符串，替换占位符

 

 

存储过程

CREATE PROC [dbo].[USP_Notification_DateSet]
(
        @TSQL nvarchar(max)='select 1 as id ,2 as name',
        @recipients nvarchar(max)='zengxinwen@sina.com',
        @profile_name nvarchar(max)=N'私人的设置Profile',
        @subject nvarchar(max)=N'查询结果通过'

)
as 
begin

declare @v_title nvarchar(max)='',         --表头
        @v_selectField nvarchar(max)='' ,  --把所有字段都拼成一行   例如 isnull(字段1，'')+','+isnull(字段2,'')
        @v_runsql nvarchar(max)='' ,       --执行SQL 语句
        @v_content nvarchar(max)='',       -- 表格内容
        
        @v_body nvarchar(max)=N'
                                    <style>
                                        table
                                        {
                                          float:left;
                                        }
                                        table, th , td  {
                                          border: 1px solid black;
                                          border-collapse: collapse;
                                          padding: 5px;
                                        }
                                        .title{
                                          background-color: #f1f1f1;
                                        }
                                        table tr {
                                          background-color: #ffffff;
                                        }
                                        table td{word-break: keep-all;white-space:nowrap;}
                                        </style>
                                        <p>大家好,</p>
                                        <p>  这是一个测试，结果如下：</p>
                                        <div>
                                            <table>
                                                @title
                                                @content
                                            </table>
                                        </div>
                                        '

        
   --1.构建临时表 
         if object_id('tempdb..#dt_info') is not null  drop table #dt_info
         CREATE TABLE #dt_info(GUID_ID_IDS int)  

         select @v_runsql+= 'ALTER TABLE #dt_info ADD '+NAME +' NVARCHAR(MAX) ;'
         from sys.dm_exec_describe_first_result_set(@TSQL ,null,0)

        
         exec( @v_runsql)
         ALTER TABLE #dt_info DROP COLUMN GUID_ID_IDS

         set @v_runsql='insert into #dt_info '+ @TSQL
         exec( @v_runsql)  --插入记录

        
     --2.构建表的标题，以及 对应字段内容拼接到 Select查询     
      
        select  @v_title=  '<tr class="title" >'+ STRING_AGG('<td>'+NAME+'</td>','')+'</tr>',
                @v_selectField =  '''<tr>'+ STRING_AGG('<td>''+ isnull('+NAME+','''')+''</td>','')+'</tr>'''
        from sys.dm_exec_describe_first_result_set('select * from #dt_info' ,null,0)


     --3.把内容输出至@v_content
     
       set @v_runsql='select  @v_content+='+@v_selectField+'  from  #dt_info ct'
       exec sp_executesql @v_runsql, N'@v_content nvarchar(max) out', @v_content out
       
    
     --4 替换占位符 并调用发送邮件

      set @v_body=replace( replace(@v_body,'@title',@v_title) ,'@content',@v_content )
                
               
                
       EXEC msdb.dbo.sp_send_dbmail @profile_name=@profile_name,
            @recipients=@recipients,
            @subject=@subject,
            @body=@v_body,
            @body_format = 'HTML'                    

END    

 

演示与结果

 1.存储过程演示   其中发邮件人（@recipients），和邮件个性设置(@profile_name)  请替换自己正确的。

if OBJECT_ID('usp_test') is not null  drop proc usp_test
go
create proc usp_test as 
begin
select name,type from sys.tables
end

GO
exec USP_Notification_DateSet  @TSQL='exec usp_test',
                               @recipients ='zengxinwen@xxxxxx',
                               @profile_name='xxxxx',
                               @subject =N'执行存储过程试试'

 

 

2.T-SQL演示

exec USP_Notification_DateSet  @TSQL='select 1 as id, 13 as num union all select 2,3',
                               @recipients ='zengxinwen@xxxxxx',
                               @profile_name='xxxxx',
                               @subject =N'执行T-SQL试试'