USP_ADD_UPDATE_BUISNESSPROCESSJOBSCHEDULES

Parameters

Parameter	Parameter Type	Mode
@SQLJobName	nvarchar(128)	IN
@SQLJobDescription	nvarchar(512)	IN
@ProxyName	nvarchar(128)	IN
@SQLJobCommand	nvarchar(max)	IN
@CreateSchedule	bit	IN
@EnableSchedule	bit	IN
@FrequencyType	int	IN
@FrequencyInterval	int	IN
@FrequencySubDayType	int	IN
@FrequencySubDayInterval	int	IN
@FrequencyRelativeInterval	int	IN
@FrequencyRecurrenceFactor	int	IN
@ActiveStartDate	char(8)	IN
@ActiveEndDate	char(8)	IN
@ActiveStartTime	char(6)	IN
@ActiveEndTime	char(6)	IN
@Action	int	IN
@ScheduleId	int	INOUT

Definition

Copy




create procedure dbo.USP_ADD_UPDATE_BUISNESSPROCESSJOBSCHEDULES
(
  @SQLJobName as sysname,
  @SQLJobDescription as nvarchar(512),
  @ProxyName as nvarchar(128),
  @SQLJobCommand as nvarchar(max),
  @CreateSchedule bit = 0,
  @EnableSchedule bit = 1,
  @FrequencyType int = 4,
  @FrequencyInterval int = 1,
  @FrequencySubDayType int = 1,
  @FrequencySubDayInterval int = 0,
  @FrequencyRelativeInterval int = 0, 
  @FrequencyRecurrenceFactor int = 0,
  @ActiveStartDate char(8) =null,
  @ActiveEndDate char(8) =null,
  @ActiveStartTime char(6) =null,
  @ActiveEndTime char(6) =null,
  @Action int =0,  --0 for Add, 1 for Both

  @ScheduleId integer = null output
)
as
begin
    set nocount on;
      declare @JobId as uniqueidentifier = null,@Schedule_Id as integer = 0; 
  if exists (select 1 from msdb.dbo.SYSJOBS where NAME = @SQLJobName)
  begin
 --edit or disable on the basis of @EnableSchedule

    if @Action = 1 --Update

    begin
    select @JobId=JOB_ID from msdb.dbo.SYSJOBS where NAME = @SQLJobName
    select @Schedule_Id = SCHEDULE_ID from msdb.dbo.SYSJOBSCHEDULES where JOB_ID=@JobId;
      exec USP_BUSINESSPROCESSJOBSCHEDULE_EDIT @Schedule_Id,@SQLJobCommand,@EnableSchedule,@FrequencyType,@FrequencyInterval,@FrequencySubDayType,
      @FrequencySubDayInterval,@FrequencyRelativeInterval,@FrequencyRecurrenceFactor,@ActiveStartDate, @ActiveEndDate,@ActiveStartTime,@ActiveEndTime
     return 2; --Updated

    end
 end

  else
  begin
   --Add the job

     if @Action =1 OR @Action = 0 --Add or Update

     begin
       exec USP_BUSINESSPROCESSJOBSCHEDULE_ADD @SQLJobName,@SQLJobDescription,@ProxyName,@SQLJobCommand,@CreateSchedule,
       @EnableSchedule,@FrequencyType,@FrequencyInterval,@FrequencySubDayType,@FrequencySubDayInterval,@FrequencyRelativeInterval,
       @FrequencyRecurrenceFactor,@ActiveStartDate,@ActiveEndDate,@ActiveStartTime,@ActiveEndTime,@ScheduleId

     return 1; --Added

     end
  end
return 0;
END