SQL Job Scheduler History

Here is an easy way to get list of jobs ran in the past with status. Do you know of a better or simpler way to get the output? If so, please use the comment area to share your code.

 

   1:  CREATE  PROCEDURE [dbo].[pr_check_JobHistory] @dateparam DATETIME = NULL
   2:  
   3:  AS
   4:  
   5:  BEGIN
   6:    IF @dateparam IS NULL
   7:    SET @dateparam = GETDATE();
   8:  
   9:    SELECT sysjobhistory.server,
  10:           sysjobs.name
  11:           AS
  12:           job_name,
  13:           CASE sysjobhistory.run_status
  14:             WHEN 0 THEN 'Failed'
  15:             WHEN 1 THEN 'Succeeded'
  16:             ELSE '???'
  17:           END
  18:           AS
  19:           run_status,
  20:           Isnull(Substring(CONVERT(VARCHAR(8), run_date), 1, 4) + '-' +
  21:                         Substring(CONVERT(VARCHAR
  22:                                   (8), run_date), 5, 2) + '-' +
  23:                  Substring(CONVERT(VARCHAR(
  24:                            8), run_date), 7, 2), '')
  25:           AS
  26:           [Run DATE],
  27:           Isnull(Substring(CONVERT(VARCHAR(7), run_time+1000000), 2, 2) + ':'
  28:                   +
  29:                         Substring(CONVERT(VARCHAR(7), run_time+1000000), 4, 2
  30:                          )
  31:                  +
  32:                  ':' +
  33:                  Substring(CONVERT(VARCHAR(7), run_time+1000000), 6, 2), '')
  34:           AS
  35:           [Run TIME],
  36:           Isnull(Substring(CONVERT(VARCHAR(7), run_duration+1000000), 2, 2) +
  37:                   ':' +
  38:                         Substring(CONVERT(VARCHAR(7), run_duration+1000000),
  39:                         4,
  40:                         2)
  41:                  + ':' +
  42:                  Substring(CONVERT(VARCHAR(7), run_duration+1000000), 6, 2),
  43:           ''
  44:           ) AS
  45:           [Duration],
  46:           sysjobhistory.step_id,
  47:           sysjobhistory.step_name,
  48:           sysjobhistory.MESSAGE
  49:    FROM   msdb.dbo.sysjobhistory
  50:           INNER JOIN msdb.dbo.sysjobs
  51:             ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
  52:    WHERE  sysjobhistory.run_date = Datepart(yyyy, @dateparam) * 10000 +
  53:                                           Datepart(mm, @dateparam) * 100 +
  54:                                    Datepart
  55:                                    (
  56:                                           dd, @dateparam)
  57:           --AND sysjobs.name = @JobName --remove this line if you want to show all jobs for the specified day
  58:    ORDER  BY instance_id DESC
  59:  END