Need help with stored procedure

I wrote a stored procedure to send an alert to recipients. The alert contains a list of applications for which a health check was not performed at this point in time. But I want my code to work as if the list is empty means there is no application that is not being tested, it should not raise a warning.

Currently, the alert is fired with an empty table and only a title.

Can 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

      

+3


source to share


2 answers


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

      

+2


source


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

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

      



You must use.

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

      

+1


source







All Articles