一个生成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