判断Agent Job完成的存储过程

/******************************************************************************
** File:    sp_WaitForJob.sql
** Name:    [dbo].[WaitForJob]

** Desc:    The proc is used to check agent job run status
**         
**
** Params:
** @jobName            -- description
** @minutesToWait      -- description
** Return
**
** Date:    2011/4/20
** ****************************************************************************
** CHANGE HISTORY
** ****************************************************************************
** Date     Author      version     1.0     #bug              Description
** ----------------------------------------------------------------------------

*/

IF OBJECT_ID ( 'WaitForJob', 'P' ) IS NOT NULL
BEGIN
 PRINT 'Dropping proc dbo.WaitForJob...'
  DROP PROCEDURE WaitForJob;
END  
GO

PRINT 'Creating proc dbo.WaitForJob...'
GO

CREATE PROCEDURE [dbo].[WaitForJob](
    @jobName varchar(100),
    @minutesToWait int
)
AS
BEGIN

    SET NOCOUNT ON;
   
    DECLARE @errorMessage varchar(250)
   
    ------------------------------------------
    -- Get the job id for the specified job.
    ------------------------------------------
    DECLARE @job_id uniqueidentifier SET @job_id = NULL
   
    SELECT @job_id = job_id
    FROM msdb.dbo.sysjobs
    WHERE name = @jobName
   
    IF @job_id IS NULL
   
    BEGIN
  
  SET @errorMessage = 'Do not find agent job with name: ' + @jobName
  RAISERROR(@errorMessage, 16, 1)
  
  RETURN
 END
 
 WAITFOR DELAY '00:00:05'
 
 -------------------------------------------------------------------------------
 -- Check to see if the job is actively running. If so, wait for job finished.  
 -- If job does not complete in specified minutes, raise error.
 -------------------------------------------------------------------------------
 DECLARE @tbl_jobActivity TABLE
 (
  session_id    int,
     job_id     uniqueidentifier,
     job_name    varchar(100),
     run_requested_date  datetime,
     run_requested_source nvarchar(50),
     queued_date    datetime,
     start_execution_date datetime,
     last_executed_step_id int,
     last_executed_step_date datetime,
     stop_execution_date     datetime,
     next_scheduled_run_date datetime,
     job_history_id          int,
     message                 varchar(2000),
     run_status              int,
     operator_id_emailed     int,
     operator_id_netsent     int,
     operator_id_pageid      int
 )
 
 DECLARE @last_executed_step_date  datetime
 DECLARE @stop_execution_date      datetime
 DECLARE @job_history_id           datetime
 
 DECLARE @startWaiting     datetime
 SET @startWaiting = getdate()
 
 WHILE(1=1) -- Loop until job is done
 
  BEGIN
   -- refresh job activity data
   DELETE FROM @tbl_jobActivity
       INSERT @tbl_jobActivity
   EXEC msdb.dbo.sp_help_jobactivity
   
   SELECT @last_executed_step_date = last_executed_step_date,
       @stop_execution_date  = stop_execution_date,
       @job_history_id   = job_history_id
   FROM   @tbl_jobActivity
   WHERE  job_id = @job_id
   
   -- Break from loop if job is no longer running and send the status message, else wait for 20 seconds
   IF
    (@last_executed_step_date IS NOT NULL)
    AND
    (@stop_execution_date IS NOT NULL)
    AND
    (@job_history_id IS NOT NULL)
    
    BREAK;
   ELSE
                WAITFOR DELAY '00:00:20'
 
   -- Raise error if waited for more minutes than specified
   IF DATEDIFF(mm, @startWaiting, getdate()) > @minutesToWait
    BEGIN
     SET @errorMessage = 'ERROR: Waited more than ' + convert(varchar,@minutesToWait) + ' minutes for job to complete.'
     RAISERROR (@errorMessage, 16, 1)
     RETURN
    END
  END
END     
GO