Automated mail report through msdb.dbo.sp_send_dbmail

Question

i want to set up one mail report. Basically it will give the name of the job which failed in last 24 hrs. I have following code. It works perfectly but the problem is if there is no job fails still I am getting one blank mail. So I would like to a make a condition; if no job fails then in stead of getting a blank mail i would like to have a message such as "No Job Failed in last 24 hrs" other wise, I will get the report with job name and all other information. Some how I am now able to set up the condition when no job fails. Any help would be highly appreciated. 

Thanks

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[Job_Failure]    Script Date: 11/19/2012 17:46:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create procedure [dbo].[Job_Failure]
as

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Job Failure Report in Last 24 hrs on Server1</H1>' +
    N'<table border="1">' +
    N'<tr><th>Job Name</th><th>Error Message</th>' +
    N'<th>Run_Date</th><th>Run_Time</th>' +
    
 CAST ( ( 
        SELECT td =sj.name, '',
			   td =sjh.message, '' ,
			   td=sjh.run_date, '',
			   td =sjh.run_time, ''
			   
		From 
	msdb..sysjobhistory sjh
	Inner Join 
		msdb..sysjobs sj 
	On 
		sjh.job_id = sj.job_id
	Inner Join 
		(	
			Select 
				job_id, max(instance_id) maxinstanceid 
			From 
				msdb..sysjobhistory 
			Where
				run_status = 0 
				And Datediff(hh, Convert(Datetime, RTrim(run_date)), Getdate()) < 24
			Group By 
				job_id
		) a 
	On 
		sjh.job_id = a.job_id And sjh.instance_id = a.maxinstanceid
		 FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;
		


EXEC msdb.dbo.sp_send_dbmail
 @recipients = '[e-mail]',
  @subject = 'Job Fail Report on Server1',
   @body = @tableHTML,
    @body_format = 'HTML' ;
    

GO

Answer 1

Put  IF - ELSE conditional logic into the stored procedure.

Articles:

http://www.databasejournal.com/features/mssql/article.php/3087431/T-SQL-Programming-Part-1---Defining-Variables-and-IFELSE-logic.htm

http://msdn.microsoft.com/en-us/library/aa933214(v=sql.80).aspx

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 2

IF (@tableHTML IS NOT NULL) -- or Check IF no job fails for the day then don't execute this SP 
EXEC msdb.dbo.sp_send_dbmail
 @recipients = '[e-mail]',
  @subject = 'Job Fail Report on Server1',
   @body = @tableHTML,
    @body_format = 'HTML' ;

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 3

Hi

if (@tableHTML is null)
   set @tableHTML = 'No Job Failed in last 24 hrs'
EXEC msdb.dbo.sp_send_dbmail
 @recipients = '[e-mail]',
  @subject = 'Job Fail Report on Server1', 
   @body = @tableHTML,
    @body_format = 'HTML' ;