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 to share