sql - 需要有关存储过程的帮助

Question

我编写了一个存储过程来向收件人发送警报。该警报包含当时尚未执行完整性测试的应用程序列表。但我想让我的代码工作，如果列表为空意味着没有未完成测试的应用程序，它不应该触发警报。

目前，警报是在空表和标题时触发的。

有人可以帮帮我吗？提前致谢。

代码如下:

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

类似的东西怎么样

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