How to retain SQL variable values in a while loop?

Question

I have below code where I want to retain @tableHTML values for every while loop iterations. Which I am not able. When I run below query I get a blank mail. Also, what is the max amount of data variable @tableHTML can hold as there would be 10-12 tables each with 20 rows. Is the blank mail due to overflow of data in @tableHTML?

If I remove @tableHTML assignment then I get data only for last logID "Set @tableHTML = @tableHTML +"

CODE:

  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

As I mentioned in the comments, it's a lot easier to use any reporting library, including SQL Server's own Reporting Services to generate and send reports as email. SSRS allows you to easily generate hierarchical reports and email delivery subscriptions.

Regarding this specific query, it doesn't need any looping. It looks like it uses SQL Server's XML support to generate a table as shown in Create HMTML from SQL Queries, but then, reverts to string concatenation to generate the outer query's HTML output.

XML as HTML works because each XML query returns a single XML value that can be part of a SELECT statement. This means, that the outer LOG query can include the table. It also means that each additional tag can be included as yet subquery in the SELECT statement.

GROUP BY isn't needed in the inner subquery since no aggregates are generated.

The outer query should be logged over LogID to avoid generating duplicates.

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')

To convert the XML value into a string, the entire result can be converted to nvarchar with CONVERT:

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')))

Did I mention it's a lot easier to use a reporting library for this?