Sending results of job execution to email

Notification email.sql

declare 
	@job_id varchar(50), @recipients nvarchar(255),
	@records int, @errors int, @r varchar(10), 
	@msg varchar(255), @subject nvarchar(255), 
	@query nvarchar(512), @procname nvarchar(255)

-- Find job id by its name
select @job_id = job_id
from msdb.dbo.sysjobs
-- replace with the name of your maintenance procedure
where name = '!. Daily notifications and maintenance - [nightly]'

-- Count all the steps with errors 
SELECT @errors = count(*)
FROM    msdb.dbo.sysjobhistory
WHERE   run_date = (SELECT MAX(run_date) FROM msdb.dbo.sysjobhistory
                                WHERE job_id = @job_id and step_id = 0)
        AND job_id = @job_id 
        AND run_status <> 1

-- You can put your condition for triggering an email here
if @errors > 0
begin
	select
		-- Semi-column separated list of recipients, replace
		-- with yours!
		@recipients = 'joe.doe@abc.com;jane.doe@abc.com',
		@msg = 
			' <b>Failed steps: ' + cast(@errors as varchar(4)) + '</b><br/><pre>',
		@subject = 
			N'SFDC Maintenance: Nightly job completed',
		@query = '
		SELECT step_name FROM msdb.dbo.sysjobhistory
		WHERE   
			run_date = (
				SELECT MAX(run_date) FROM msdb.dbo.sysjobhistory
                WHERE job_id = ''' + @job_id + ''' and step_id = 0)
        AND job_id = ''' + @job_id + ''' 
        AND run_status <> 1'

	EXEC msdb.dbo.sp_send_dbmail
		@recipients = @recipients,
		@body = @msg,
		@subject = @subject,
		@query = @query,
		@profile_name ='Default notification profile',
		@body_format = 'HTML',
		@query_result_header = 0
end