USP_BUSINESSPROCESSSTATUS_VALIDATESTATUS

CREATE procedure dbo.USP_BUSINESSPROCESSSTATUS_VALIDATESTATUS
(
  @BUSINESSPROCESSSTATUSID uniqueidentifier = null
)
with execute as caller
as
begin
  set nocount on

  declare @STATUSRECORDS table (ID uniqueidentifier);
  declare @STATUSNEEDUPDATE bit = 0; 

  if @BUSINESSPROCESSSTATUSID is not null
  begin
    insert into @STATUSRECORDS
    select
      ID
    from
      dbo.BUSINESSPROCESSSTATUS
    where
      ID = @BUSINESSPROCESSSTATUSID and
      STATUSCODE = 1 and
      applock_test('public', upper(cast(ID as nvarchar(36))), 'Exclusive', 'Session') = 1;

    if @@ROWCOUNT > 0
      set @STATUSNEEDUPDATE = 1;
  end
  else
  begin
    insert into @STATUSRECORDS
    select
      ID
    from
      dbo.BUSINESSPROCESSSTATUS
    where
      STATUSCODE = 1 and
      applock_test('public', upper(cast(ID as nvarchar(36))), 'Exclusive', 'Session') = 1;

    if @@ROWCOUNT > 0
      set @STATUSNEEDUPDATE = 1;
  end

  if @STATUSNEEDUPDATE = 1
  begin
    declare @CHANGEAGENTID uniqueidentifier;
    declare @CHANGEDATE datetime = getdate();

    if @CHANGEAGENTID is null
      exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    update
      BPS
    set
      BPS.STATUSCODE = 2,
      BPS.ERRORMESSAGE = 'Process terminated unexpectedly.',
      BPS.ENDEDON = @CHANGEDATE,
      BPS.CHANGEDBYID = @CHANGEAGENTID,
      BPS.DATECHANGED = @CHANGEDATE
    from
      dbo.BUSINESSPROCESSSTATUS BPS
    inner join
      @STATUSRECORDS SR on SR.ID = BPS.ID;
  end
end