sqlserver 邮件提醒（sql 转换成html table）

左手的ㄟ右手 2022-05-10 12:00 171阅读 0赞

1.将查询语句结果集封装成html table

2.将结果放入到待发送表中

3.通过sqlserver定时job循环表，调用邮件组件发送提醒邮件。

/*调用示例*/ 
     --根据sql语句，将指定邮件信息插入待发送邮件列表
     --此种情况仅时候管理员提醒，收件人是参数指定
    
     EXEC [P_Monitor_NewOfferAlertToAdminByDate] '2014-4-1','2018-10-1'
     SELECT* FROM  datamonitor_email
    
    
     --新增存储过程
     go
     ALTER PROCEDURE [dbo].[P_Monitor_NewOfferAlertToAdminByDate]
     ( @beginDate NVARCHAR(100),
      @endDate NVARCHAR(100)
     )
     AS 
     BEGIN
     --获取数据
       
    DECLARE @body NVARCHAR(MAX); 
    declare @qWHERE nvarchar(500);  
    SET @qWHERE= ' Where  CreateTime Between ''' + @beginDate+''' And '''+ @endDate+''' ';
     EXEC dbo.[P_GetHtmlTableStringWithSqlQuery]
        N' Select top 5 ', --@qSELECT  
        N' StaffCode as 员工编码,LTINumber,LTITypeId', --@fieldlist  
        N'From    Mng_OfferResultInfo  ', --@qFROM  
        @qWHERE , --   @qWHERE  
        '', --@qGroupBy  
        '', --@qHaving  
        ' ', --@qOrderBy  
        '标题：近期data', --@HtmlTableTitle 
        @body OUTPUT 
      PRINT @body
     ----生成邮件内容---- 
    declare @tdata nvarchar(500); 
    declare @tableformat nvarchar(500);
    declare @thead  nvarchar(500); 
    declare @bodytext  nvarchar(500); 
    declare @subjecttext nvarchar(500); 
    declare @reciveaddress nvarchar(500);
    declare @expectedsenttime datetime ;
     --插入待发送列表
     insert into  datamonitor_email select '测试','123@qq.com' ,  '标题：近期新增的data' , @body  ,' ',GETDATE(),null,'N',' ',0 ;
     RETURN 0
     END 
    
     -----------------------------------------------------------/下列为存储过程创建和方法的创建/---------------------------------------------------------------------------------
    GO
    ALTER PROCEDURE [dbo].[P_GetHtmlTableStringWithSqlQuery]
    (
       @qSELECT NVARCHAR(100), --The select part of the sql statement, which can include top X
       @fieldlist NVARCHAR(MAX), --Pipe delimited list of fields, which can include aliases
       @qFROM NVARCHAR(MAX), --The from part of the sql statment, which can include joins
       @qWHERE NVARCHAR(MAX) = '', --The where part of the sql statement
       @qGroupBy NVARCHAR(MAX) = '',--The group by clause
       @qHaving NVARCHAR(MAX) = '',--The having clause
       @qOrderBy NVARCHAR(MAX) = '', --The order by part of the sql statement  
       @HtmlTableTitle NVARCHAR(4000) = '', --The title of the html table that holds the query results 
       @body NVARCHAR(MAX) OUTPUT
    )
    
    AS
    BEGIN
    
       --Declare initial variable.
       DECLARE @xml NVARCHAR(MAX)
    
       DECLARE @sql NVARCHAR(MAX)
       DECLARE @resultexist NVARCHAR(MAX)
       DECLARE @tblfieldheader NVARCHAR(MAX) = ''
       DECLARE @tempfield NVARCHAR(MAX) = ''
       CREATE TABLE #Fields (ID INT IDENTITY(1,1),field NVARCHAR(MAX))
       DECLARE @i INT = 1, @j INT = 0, @SendEmail INT
       DECLARE @splitcnt INT
       DECLARE @fieldcount INT  
      
       --Find the number of fields in the query  
       SELECT @splitcnt = LEN(@fieldlist)-LEN(REPLACE(@fieldlist,',',''))
       --Loop through the fields and put each on into the #Fields temp table as a new record
       WHILE @j <= @splitcnt
           BEGIN
               INSERT INTO #Fields ( field ) SELECT s FROM  dbo.SplitString(@fieldlist,',') WHERE zeroBasedOccurance = @j
               SET @j += 1
           END
       
       SELECT @fieldcount = @splitcnt + 1 --Will be the splitcnt + 1, otherwise MAX(ID) FROM #Fields  
    
       --Start setting up the sql statement for the query.
       SET @sql = @qSELECT
       --Loop through the #Fields table to get the field list
       WHILE @i <= @fieldcount
           BEGIN
               SELECT @tempfield = field FROM #Fields WHERE ID = @i
               --------------------------------------------------------------------------------------------------------------------------------------------------------------
               --This next section is required in case a field is aliased.  For the xml, we need to get rid of the aliases, the table header will only require the aliases.
               --NULL values need to be shown as a string = 'NULL' or the html table will just skip the cell and all values after that in the row will be shifted left.
               ---------------------------------------------------------------------------------------------------------------------------------------------------------------
               IF RIGHT(@tempfield,1) = ']' OR CHARINDEX(' as ',@tempfield) = 0
                   BEGIN
                       --Set the xml field to be the entire field name
                       SET @sql = @sql + ' ISNULL(CAST(' + @tempfield + ' AS NVARCHAR(4000)),''NULL'') AS ''td'','''','
                       --Set the table header field to be the entire field name
                       SET @tblfieldheader = @tblfieldheader + '<th>' + @tempfield + '</th>'
                   END          
               ELSE 
                   BEGIN
                       --Set the xml field to be the field name minus the alias
                       SET @sql = @sql + ' ISNULL(CAST(' + LEFT(@tempfield,LEN(@tempfield) - (CHARINDEX(' sa ',REVERSE(@tempfield))+3)) + ' AS NVARCHAR(4000)),''NULL'') AS ''td'','''','
                       --Set the table header field to be the field name's alias
                       SET @tblfieldheader = @tblfieldheader + '<th>' + RIGHT(@tempfield,CHARINDEX(' sa ',REVERSE(@tempfield))-1) + '</th>'
                   END
               --Increment the counter.
               SET @i += 1
           END
       --Trim the extra four characters of the end of @sql.      
       SET @sql = LEFT(@sql, LEN(@sql)-4)
       --Add the from, where, group by, having, and order by clause to the select statement.
       SET @sql = @sql + ' ' + @qFROM + ' ' + @qWHERE + ' ' +  @qGroupBy + ' ' + @qHaving + ' ' + @qOrderBy
    
       --Put the set xml command around the sql statement.
       SET @sql = 'SET @XML = CAST(( ' + @sql + ' FOR XML PATH(''tr''),ELEMENTS ) AS NVARCHAR(MAX))'
          PRINT @sql
       --Run the sql that will create the xmll.
       EXEC sp_executesql @sql, N'@xml nvarchar(max) output', @xml OUTPUT
       
       --Create the body of the email, which contains the xml results of the query.
       SET @body = '<html><body><H3>' + @HtmlTableTitle + '</H3><table border = 1><tr>' + @tblfieldheader + '</tr>' + @xml + '</table></body></html>'
    
       --Drop the fields temp table.
       DROP TABLE #Fields
    
     --select @body as returnvalue  
    END
    
    GO
    --拆分方法
    CREATE FUNCTION [dbo].[SplitString] 
        (
            @str NVARCHAR(4000), 
            @separator CHAR(1)
        )
        RETURNS TABLE
        AS
        RETURN (
            WITH tokens(p, a, b) AS (
                SELECT 
                    1, 
                    1, 
                    CHARINDEX(@separator, @str)
                UNION ALL
                SELECT
                    p + 1, 
                    b + 1, 
                    CHARINDEX(@separator, @str, b + 1)
                FROM tokens
                WHERE b > 0
            )
            SELECT
                p-1 zeroBasedOccurance,
                SUBSTRING(
                    @str, 
                    a, 
                    CASE WHEN b > 0 THEN b-a ELSE 4000 END) 
                AS s
            FROM tokens
          )