一个生成job的存储过程

 

ALTER   PROCEDURE   [ dbo ] . [ CreateJob ]  
     @jobName          varchar ( 128 ),     
     @serverName      sysname  =   '' ,   
     @dbname          sysname  =   '' , 
     @stepName         varchar ( 50 )  =   '' ,
     @scheduleName     varchar ( 50 )  =   '' ,
     @stepSQL          varchar ( 5000 )  =   '' ,
     @freqtype         varchar ( 6 )  =   '' ,  -- month, week, day, ''
     @freqsubdaytype   varchar ( 6 )  =   ' time ' ,  -- time, minute
     @interval         int   =   - 1 ,
     @date             int   =   00000000 ,     --
     @time             int   =   000000
AS
BEGIN
     SET  NOCOUNT  ON ;
     BEGIN   TRANSACTION ;
     IF   ISNULL ( @dbname , '' )  =   ''    
     BEGIN
         SET   @dbname   =   DB_NAME ();
     END ;
     -- 删除已经存在的job
     DECLARE   @delJob   int ;
     EXEC   @delJob = DeleteJob  @jobName = @jobName ;
     IF  ( @delJob   NOT   IN  ( 0 ,  1 ))
     BEGIN
         RETURN  ( 1 );   -- 现有job删除失败
     END
     -- add job
     BEGIN  TRY
         EXEC  msdb..sp_add_job 
             @job_name       =   @jobname ,
             @delete_level   =   1 ;         -- run one time, then delete it
     END  TRY
     BEGIN  CATCH
         ROLLBACK   TRANSACTION ;
         RETURN  ( 2 );
     END  CATCH
     -- add step
     IF   ISNULL ( @stepName , '' )  <>   ''
     BEGIN
         BEGIN  TRY
             EXEC  msdb..sp_add_jobstep 
                 @job_name         =   @jobname ,  
                 @step_name        =   @stepName ,  
                 @subsystem        =   ' TSQL ' ,  -- TSQL is default value 
                 @database_name    =   @dbname ,  
                 @command          =   @stepSQL ,  
                 @retry_attempts   =   5 ,  
                 @retry_interval   =   5 ;
         END  TRY
         BEGIN  CATCH
             ROLLBACK   TRANSACTION ;
             RETURN  ( 3 );
         END  CATCH
     END ; 
     -- add job schedule
     IF   ISNULL ( @scheduleName , '' )  <>   ''
     BEGIN
         DECLARE   @ftype   int ,  @fstype   int ,  @ffactor   int ;
         DECLARE   @strJobSchedule   varchar ( 5000 );
         SET   @strJobSchedule   =   ' msdb..sp_add_jobschedule '   +   CHAR ( 13 )  +
             ' @job_name= '''   +   @jobname   +   ''' , '   +   CHAR ( 13 )  +   ' @name= '''   +
             @scheduleName   +   ''' , '   +   CHAR ( 13 );          
         -- set freq_type 
         SET   @ftype   =   CASE   @freqtype
             WHEN   ''        THEN   1
             WHEN   ' day '     THEN   4   
             WHEN   ' week '    THEN   8   
             WHEN   ' month '   THEN   16
         END ;    
         -- set freq_subday_type
         SET   @fstype   =   CASE   @freqsubdaytype   
             WHEN   ' time '   THEN   0x1
             WHEN   ' minute '   THEN   0x4
         END ; 
         -- set freq_recurrence_factor
         SET   @ffactor   =   CASE   @freqtype    
             WHEN   ''      THEN   0
             WHEN   ' day '   THEN   0
             ELSE   1    
         END ;         
         IF   ISNULL ( @freqtype , '' )  <>   ''
         BEGIN
             DECLARE   @sInterval   int ;
             IF   @freqsubdaytype   =   ' time '
             BEGIN
                 SET   @sInterval   =   @time ;
             END
             ELSE   IF   @freqsubdaytype   =   ' minute '
             BEGIN
                 SET   @sInterval   =   @interval ;
             END
             ELSE
             BEGIN
                 RETURN ( 4 );
             END ;
             SET   @strJobSchedule   =   @strJobSchedule   +   ' @freq_type= '   +
                 CAST ( @ftype   AS   varchar ( 10 ))  +   ' , '   +   CHAR ( 13 )  +   ' @freq_interval= ' +
                 CAST ( @interval   AS   varchar ( 10 ))  +   ' , '   +   CHAR ( 13 )  + ' @freq_subday_type= ' +
                 CAST ( @fstype   AS   varchar ( 10 ))  +   ' , '   +   CHAR ( 13 )  + ' @freq_subday_interval= '   +
                 CAST ( @sInterval   AS   varchar ( 10 ))  +   ' , '   +   CHAR ( 13 );
         END
         ELSE
         BEGIN
             SET   @strJobSchedule   =   @strJobSchedule   +   +   ' @freq_type= '   +
                 CAST ( @ftype   AS   varchar ( 10 ))  +   ' , '   +   CHAR ( 13 )  +   ' @freq_subday_type= ' +
                 CAST ( @fstype   AS   varchar ( 10 ))  +   ' , '   +   CHAR ( 13 )  +   ' @freq_subday_interval= '   +
                 CAST ( @time   AS   varchar ( 10 ))  +   ' , '   +   CHAR ( 13 );
         END ;
         SET   @strJobSchedule   =   @strJobSchedule   +   ' @freq_recurrence_factor= '   +
             CAST ( @ffactor   AS   varchar ( 10 ))  +   ' , '   +   CHAR ( 13 )  +   ' @active_start_date= '   +
             CAST ( @date   AS   varchar ( 10 ))  +   ' , '   +   CHAR ( 13 )  + ' @active_start_time= '   +
             CAST ( @time   AS   varchar ( 10 ))  +   ' ; ' ;   
         BEGIN  TRY    
             EXEC  ( @strJobSchedule );
         END  TRY
         BEGIN  CATCH
             ROLLBACK   TRANSACTION ;
             RETURN  ( 5 );
         END  CATCH
         -- server name
         IF   ISNULL ( @servername , '' )  =   ''
         BEGIN
             SET   @servername   =   @@servername ;  
         END ;
         -- add job server
         BEGIN  TRY
         EXEC  msdb..sp_add_jobserver
             @job_name      =   @jobname ,    
             @server_name   =   @servername ;  
         END  TRY
         BEGIN  CATCH
             ROLLBACK   TRANSACTION ;
             RETURN  ( 6 );
         END  CATCH       
     END ;
     COMMIT   TRANSACTION ;
     RETURN ( 0 );
END