如何在while循环中保留SQL变量值？

Question

在下面的代码中，我希望为每隔一次循环迭代保留@tableHTML值。这是我做不到的当我在查询下面运行时，会收到一封空白邮件。此外，数据变量@tableHTML可以容纳的最大数量是多少，因为每个表有10-12个表，每个表有20行。空邮件是由于@tableHTML中的数据溢出造成的吗？

如果删除@tableHTML赋值，那么我只获得最后一个logID "Set @tableHTML = @tableHTML +“的数据。

代码：

  DECLARE @tableHTML NVARCHAR(MAX)
  while exists(select * from #temptable)
  begin
    select top 1 @logID = logID
    from #temptable
    Set @tableHTML = @tableHTML +
    N'<H2>Log =' +@logID+'</H2>'+
    N'<H3>List of tests failing in last 24 hours</H3>' +
    N'<table border="1" BORDERCOLOR="#336699" CELLPADDING="2"' +
    N'<tr><th>TestName</th><th>OS</th><th>Branch</th><th>Link</th>
    <th>Failure Rate (%)</th>' +
    N'<th>Failure Count</th><th>Run Count</th></tr>' +
    CAST ( ( SELECT 
                td=TestName, '',
                td=OS, '',
                td=Branch, '',
                td=HTMLLink, '',
                td=FailureRate , '',
                td=FailureCount, '',
                td=TotalRuns, ''
            FROM RepeatedFailingTest 
            WHERE logID = @logID
            GROUP BY 
            TestName,OS,Branch,HTMLLink,FailureRate,
            FailureCount,TotalRuns
            FOR XML PATH('tr'), TYPE
            ) AS NVARCHAR(MAX) ) +
    N'</table>' +
    N'<br><br>'+
    N'</font>';
    delete #temptable
    where logID =@logID
  end
EXEC msdb.dbo.sp_send_dbmail 
@recipients=@myrecipients, 
@body=@tableHTML,
@body_format='HTML', 
@subject=@mysubject,
@profile_name='Log notification system'

Answer 1

正如我在注释中提到的，使用任何报告库(包括Server自己的reporting )生成和发送报告都要容易得多。SSRS允许您轻松地生成分层报告和电子邮件传递订阅。

对于这个特定的查询，它不需要任何循环。它似乎使用Server的XML支持来生成一个表，如从SQL查询创建HMTML所示，但随后恢复为字符串连接，以生成外部查询的HTML。

XML作为HTML工作，因为每个XML查询返回一个XML值，该值可以是SELECT语句的一部分。这意味着外部日志查询可以包括表。这还意味着每个附加的标记都可以包含在SELECT语句中。

内部子查询中不需要组BY，因为没有生成聚合。

外部查询应记录在LogID上，以避免生成重复查询。

declare @temptable table (logid integer)

declare @RepeatedFailingTest table (logid int,TestName nvarchar(20),OS nvarchar(20),
                                    Branch nvarchar(20),HTMLLink nvarchar(40),
                                    FailureRate int, FailureCount int ,TotalRuns int)

insert into @temptable values (1),(2)

insert into @RepeatedFailingTest (logid ,TestName ,OS ,Branch ,HTMLLink ,FailureRate , FailureCount ,TotalRuns )
values
(1,'aa','Windows','A1','http://www.google.com',30, 30,100),
(1,'ab','Windows','A1','http://www.google.com',30, 30,100),
(2,'a1','Windows','A1','http://www.google.com',30, 30,100)

select 
    -- Generate H2 here
    (SELECT 'Log = ' + FORMAT(g.logid,'d')  as h2 FOR XML PATH(''), ELEMENTS,TYPE ) ,   
    -- Generate H3 here
    (SELECT 'List of tests failing in last 24 hours' as h3 FOR XML PATH(''), TYPE) ,
    -- Generate the table
    (SELECT     
        --Headings
        (SELECT 'Row' as th, 'TestName' as th , 'OS' as th , 'Branch' as th, 
                'HTMLLink' as th , 'FailureRate' as th, 'FailureCount' as th, 
                'TotalRuns' as th
            FOR XML RAW('tr'), ELEMENTS, TYPE) AS 'thead',
        -- Rows
        (
        SELECT 
          -- Row number
          ROW_NUMBER() OVER(ORDER BY TestName ,OS ,Branch ,HTMLLink ,
                                     FailureRate,FailureCount,TotalRuns)  td,
          TestName as td ,OS as td ,Branch as td,HTMLLink as td ,
          FORMAT(FailureRate,'d') as td,FORMAT(FailureCount,'d') as td,FORMAT(TotalRuns,'d') AS td
          FROM @RepeatedFailingTest f
          where g.logid=f.logid
          ORDER BY TestName ,OS ,Branch ,HTMLLink ,FailureRate,FailureCount,TotalRuns
        FOR XML RAW('tr'), ELEMENTS, TYPE
        ) AS 'tbody'
      FOR XML PATH(''), ROOT('table'),elements, type) 
from @RepeatedFailingTest g
group by g.logid
FOR XML PATH(''), ROOT('body')

要将XML值转换为字符串，可以使用CONVERT将整个结果转换为nvarchar。

select convert(nvarchar(4000),(
select 
    -- Generate H2 here
    (SELECT 'Log = ' + FORMAT(g.logid,'d')  as h2 FOR XML PATH(''), ELEMENTS,TYPE ) ,   
    -- Generate H3 here
    (SELECT 'List of tests failing in last 24 hours' as h3 FOR XML PATH(''), TYPE) ,
    -- Generate the table
    (SELECT     
        --Headings
        (SELECT 'Row' as th, 'TestName' as th , 'OS' as th , 'Branch' as th, 
                'HTMLLink' as th , 'FailureRate' as th, 'FailureCount' as th, 
                'TotalRuns' as th
            FOR XML RAW('tr'), ELEMENTS, TYPE) AS 'thead',
        -- Rows
        (
        SELECT 
          -- Row number
          ROW_NUMBER() OVER(ORDER BY TestName ,OS ,Branch ,HTMLLink ,
                                     FailureRate,FailureCount,TotalRuns)  td,
          TestName as td ,OS as td ,Branch as td,HTMLLink as td ,
          FORMAT(FailureRate,'d') as td,FORMAT(FailureCount,'d') as td,FORMAT(TotalRuns,'d') AS td
          FROM @RepeatedFailingTest f
          where g.logid=f.logid
          ORDER BY TestName ,OS ,Branch ,HTMLLink ,FailureRate,FailureCount,TotalRuns
        FOR XML RAW('tr'), ELEMENTS, TYPE
        ) AS 'tbody'
      FOR XML PATH(''), ROOT('table'),elements, type) 
from @RepeatedFailingTest g
group by g.logid
FOR XML PATH(''), ROOT('body')))

我有没有提过用一个报告库来处理这个问题要容易得多？