T-SQL Stored Procedure to Get the Last Job Run Time and Status

[sourcecode language=”sql”]
/*
Stored procedure to run the last run time and run satus of a SQL job.

SQL Version: 2005+

Last modified on 2013-07-03 by Nick Xu

–Usage
–returns the last successful run time
exec sp__GetLastJobRunTimeStatus @jobname = N’syspolicy_purge_history’, @status = N’success’

–returns the last failed runtime
exec sp__GetLastJobRunTimeStatus @jobname = N’syspolicy_purge_history’, @status = N’fail’
*/
use master
go

if exists (select * from sysobjects where name = N’sp__GetLastJobRunTimeStatus’ and type = N’P’)
begin
drop proc sp__GetLastJobRunTimeStatus
end
go

create proc sp__GetLastJobRunTimeStatus
@jobname as varchar(100) = N’syspolicy_purge_history’,
@status as varchar(10) = N’success’
as
begin

declare @run_status as int

if (upper(@status) = N’FAIL’)
begin
set @run_status = 0
end
else
begin
set @run_status = 1
end

SELECT j.[name] as JobName,

"RunSatus" =
case
when jh.run_status = 1 then N’SUCCESS’
else N’FAIL’
end,

MAX(CAST(STUFF(STUFF(CAST(jh.run_date as varchar),7,0,’-‘),5,0,’-‘) + ‘ ‘ + STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),’ ‘,’0′),5,0,’:’),3,0,’:’) as datetime)) AS [LastRunTime]
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh
ON jh.job_id = j.job_id AND jh.step_id = 0
WHERE j.[name] = @jobname
GROUP BY j.[name], jh.run_status
HAVING jh.run_status = @run_status
end
go
[/sourcecode]