Send Sql server agent job notifications Via Sql Script

Today I have to add some notifications about the status of specific sql server agent jobs, specially if the job fail and been a lazy programmer I create some metadata tables with specific messages and jobs, but I still want to share the core code used to send this message. Because this is set on a ssis sql task we only care to see a message if the job fails so, top 50 should be enough history to know something is wrong :-) .

Cheers.

declare @sqlcmd varchar(2000) = 'set transaction isolation level read uncommitted;

set nocount on; declare @today varchar(8) = convert (varchar(8),getdate(),112)

SELECT  top 50  j.name JobName ,

        h.step_name StepName ,

        CONVERT(CHAR(10), CAST(STR(h.run_date, 8, 0) AS DATETIME), 111) RunDate ,

        STUFF(STUFF(RIGHT(''000000'' + CAST (h.run_time AS VARCHAR(6)), 6), 5, 0,

                    '':''), 3, 0, '':'') RunTime ,

        h.run_duration StepDuration ,

        CASE h.run_status

          WHEN 0 THEN ''failed''

          WHEN 1 THEN ''Succeded''

          WHEN 2 THEN ''Retry''

          WHEN 3 THEN ''Cancelled''

          WHEN 4 THEN ''In Progress''

        END AS ExecutionStatus ,

        h.message MessageGenerated

FROM    sysjobhistory h

        INNER JOIN sysjobs j ON j.job_id = h.job_id

where j.name = ''Job_name Here''

and h.run_status in(1,0,3) and h.run_date >= @today

ORDER BY h.instance_id desc',

@today varchar(50) = 'NameOfAttachFileHere';

set @today = @today+'_'+ replace (replace(replace(convert(varchar(25),

getdate(),

120),

'-',

''),

':',

''),

' ',

'') +'.csv';

EXEC msdb.dbo.sp_send_dbmail

    @recipients = 'EmailRecipientsHere',

               @body= 'EmailBodyHere',

               @execute_query_database = 'msdb',

    @query = @sqlcmd,

    @subject = 'Subject Alert Here',

               @attach_query_result_as_file = 1,

               @query_result_separator = ',

',

               @query_result_no_padding = 1,

               @query_attachment_filename = @today;