Need assistance with stored procedure

Question

I have written a stored procedure to send an alert to the recipients. The alert contains the list of applications for which the sanity testing hasn't been performed at that point of time. But I want to make my code work like if the list is empty means there are no application whose testing isn't done, it should not trigger the alert.

Currently, the alert is triggered with an empty table and only with the heading.

Could someone please help me? Thanks in advance.

Here is the code:

CREATE PROCEDURE [dbo].[sp_Alert_BellTV_Chip Report]
AS
    declare @HtmlBody varchar(max) = null

    select appname, metalplating 
    from v_Escalation_lvl_3 
    where LVL3_ESCLS = 'Bell TV'

BEGIN
       create table dbo.##BellTV_Application(Position int identity(1,1),html varchar(max))

       insert into dbo.##BellTV_Application(html)
       select distinct('<tr style="color:White;background-color:#666666;white-space:nowrap;"><td>'+appname+'</td><td>'+metalplating+'</td></tr>') 
       from dbo.v_Escalation_lvl_3
       where LVL3_ESCLS = 'Bell TV'

            DECLARE @cnt_2 INT = 1;
            declare @subject varchar(max)='Chip Report'

            set @HtmlBody=''
            set @HtmlBody='<table cellspacing="0" cellpadding="4" border="0" style="color:#333333;font-family:Century Gothic;width:50%;border-collapse:collapse;">
            <tr><td>Hi</td></tr><tr><td></td></tr><tr><td colspan=\"2\">Could you please perform a sanity test for the below applications.</td></tr><tr><td></td></tr>
            <tr style="color:White;background-color:#336699;"><td>Application Name</td><td>Metal Plating</td></tr>'

            while @cnt_2 <=(select  count(*) from dbo.##BellTV_Application)
            begin
            set @HtmlBody=@HtmlBody+(select html from dbo.##BellTV_Application where Position=@cnt_2)
            SET @cnt_2 = @cnt_2 + 1;
            end

            set @HtmlBody=@HtmlBody+'<tr><td>Thanks </td></tr><tr><td>Sanity Tool</td></tr></table>'
            select @HtmlBody
            drop table  dbo.##BellTV_Application                    

            end 
            if @HtmlBody<>'' or @HtmlBody is not null
            begin

            declare @aemail varchar(Max)
            set @aemail ='recipeints'

            EXEC sp_sendEmailToStartTesting
                    @mailRecipients = @aemail,
                    @mailbody = @htmlbody, 
                    @mailSubject = @subject
END
Go

Answer 1

I think instead of checking @HtmlBody you should check @cnt_2 value (Initially it is 1)

if @HtmlBody<>'' or @HtmlBody is not null
            begin
            ..........

You should use.

 if  @cnt_2>1
            begin
            ..........   

Answer 2

What about something like

BEGIN
   create table dbo.##BellTV_Application(Position int identity(1,1),html varchar(max))
   insert into dbo.##BellTV_Application(html)
   select distinct('<tr style="color:White;background-color:#666666;white-space:nowrap;"><td>'+appname+'</td><td>'+metalplating+'</td></tr>') 
   from dbo.v_Escalation_lvl_3
   where LVL3_ESCLS = 'Bell TV'

        DECLARE @cnt_2 INT = 1;
        DECLARE @cntTotal INT

        SELECT @cntTotal = count(*) from dbo.##BellTV_Application
  IF @cntTotal >0
      BEGIN
        declare @subject varchar(max)='Chip Report'
        set @HtmlBody=''
        set @HtmlBody='<table cellspacing="0" cellpadding="4" border="0" style="color:#333333;font-family:Century Gothic;width:50%;border-collapse:collapse;">
        <tr><td>Hi</td></tr><tr><td></td></tr><tr><td colspan=\"2\">Could you please perform a sanity test for the below applications.</td></tr><tr><td></td></tr>
        <tr style="color:White;background-color:#336699;"><td>Application Name</td><td>Metal Plating</td></tr>'

        while @cnt_2 <=@cntTotal
        begin
        set @HtmlBody=@HtmlBody+(select html from dbo.##BellTV_Application where Position=@cnt_2)
        SET @cnt_2 = @cnt_2 + 1;
        end

        set @HtmlBody=@HtmlBody+'<tr><td>Thanks </td></tr><tr><td>Sanity Tool</td></tr></table>'
        select @HtmlBody
        drop table  dbo.##BellTV_Application                    

        end 
        if @HtmlBody<>'' or @HtmlBody is not null
        begin

        declare @aemail varchar(Max)
        set @aemail ='recipeints'

        EXEC sp_sendEmailToStartTesting
                @mailRecipients = @aemail,
                @mailbody = @htmlbody, 
                @mailSubject = @subject
END   END  GO