Cacher is the code snippet organizer for pro developers

declare @EmailRecipients as varchar(1000),
		@MailProfileName as varchar(255),
		@Msg as varchar(1000),
		@NumofFails as smallint,
		@JobName as varchar(1000),
		@Subj as varchar(1000),
		@i as smallint = 1

select @EmailRecipients = email_address 
from msdb.dbo.sysoperators
where [name] = 'AssetMgmt_OP'

set @MailProfileName = 'SQL Mail Profile'

select * into #Errs
from
	(	select	rank() over (partition by step_id order by step_id) rn, 
				row_number() over (partition by step_id order by run_date desc, run_time desc) ReverseTryOrder,
				j.[name] job_name,
				run_status,
				step_id,
				step_name,
				[message]
		from msdb.dbo.sysjobhistory h
		join msdb.dbo.sysjobs j ON j.job_id = h.job_id
		where instance_id > coalesce((	select max(instance_id) from msdb.dbo.sysjobhistory
										where job_id = $(ESCAPE_SQUOTE(JOBID)) 
										and step_id = 0), 0)
		and h.job_id = $(ESCAPE_SQUOTE(JOBID))
	) AS agg
where ReverseTryOrder = 1 ---Pick the last retry attempt of each step
and run_status <> 1 -- show only those that didn't succeed 


set @NumofFails = coalesce(@@rowcount,0)---Stored here because we'll still need the rowcount after it's reset.

if  @NumofFails <> 0
begin
    declare @PluralS as char(1) = case when @NumofFails > 1 then 's' else '' end

    select top 1	@Subj = job_name + ':' + cast(@NumofFails as varchar(3)) + '''Check Steps'' Report',
					@Msg =  '''Check Steps'' has reported that one or more Steps failed during execution of ' + job_name + char(13) + char(10) + char(13) + char(10)
    from #Errs

    while @i <= @NumofFails 
    begin
        select @Msg = @Msg + 'Step ' + cast(step_id as varchar(3)) + ': ' + step_name + char(13) + char(10) + [message] + char(13) + char(10) + char(13) + char(10) 
        from #Errs
        where rn = @i

        set @i = @i + 1
    end

    exec msdb.dbo.sp_send_dbmail
    @recipients = @EmailRecipients,
    @subject = @Subj,
    @profile_name = @MailProfileName,
    @body = @Msg
end