UFN_JOBOCCURRENCE_BASECURRENCIESMATCH

create function dbo.[UFN_JOBOCCURRENCE_BASECURRENCIESMATCH]
(
  @BASECURRENCYID uniqueidentifier,
  @JOBID uniqueidentifier,
  @EVENTID uniqueidentifier
)
returns bit
with execute as caller
as
  begin
    declare @MATCH bit;
    declare @JOBEXISTS bit;
    declare @EVENTEXISTS bit;

    set @MATCH = 1;

    set @JOBEXISTS = 0;
    select @JOBEXISTS = 1 from dbo.[JOB] where [ID] = @JOBID;
    set @EVENTEXISTS = 0;
    if @EVENTID is not null select @EVENTEXISTS = 1 from dbo.[EVENT] where [ID] = @EVENTID;

    if not @BASECURRENCYID is null
      if @JOBEXISTS = 1
        set @MATCH = case when exists (select top 1 1 from dbo.[JOB] where [ID] = @JOBID and [BASECURRENCYID] = @BASECURRENCYID) then 1 else 0 end;
      -- else it's another error entirely


    -- if @BASECURRENCYID is null, the trigger on the job occurrence table is going to set it to match that of the job, so the point is moot


    if @MATCH = 1 and @EVENTID is not null and @JOBEXISTS = 1
      begin
        -- again, if @BASECURRENCYID is null, the trigger on the job occurrence table is going to set it to match that of the job

        if @BASECURRENCYID is null select @BASECURRENCYID = [BASECURRENCYID] from dbo.[JOB] where [ID] = @JOBID;
        if @EVENTEXISTS = 1
          set @MATCH = case when exists (select top 1 1 from dbo.[EVENT] where [ID] = @EVENTID and [BASECURRENCYID] = @BASECURRENCYID) then 1 else 0 end;
        -- else it's another error entirely

      end

    return @MATCH;
  end