USP_BUSINESSPROCESSSTATUS_UPDATESTATUS

      CREATE procedure dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS
      (
        @ID uniqueidentifier,
        @STATUSMESSAGE nvarchar(1000),
        @STATUSCODE int, 
        @CHANGEAGENTID uniqueidentifier,
        @INNEREXCEPTION nvarchar(max) = null
      )
      with execute as caller
      as
      begin
        set nocount on;

        declare @SQL nvarchar(2000);
        declare @TABLENAME nvarchar(100);
        declare @CURRENTSTATUS int;
        declare @CURRENTDATE datetime;
        declare @ERRORMSG nvarchar(max);
        declare @INNERMSG nvarchar(max);

        if @STATUSCODE = 2
          begin
            set @ERRORMSG = @STATUSMESSAGE;
            set @INNERMSG = @INNEREXCEPTION;
          end
        else
          begin
            set @ERRORMSG = '';
            set @INNERMSG = null;
          end

        set @TABLENAME = '##BUSINESSPROCESSSTATUS_' + replace(cast(@ID AS nvarchar(36)),'-','_');

        if charindex('''', @STATUSMESSAGE) > 0
          set @STATUSMESSAGE = replace(@STATUSMESSAGE, '''', '''''');

        --For enqueque, if the sql server resets before the process starts to process, the temp status table is removed so create the status table here

        --and insert the status message

        set @SQL = 'if object_id(''tempdb..' + @TABLENAME + ''') is null' + char(13);
        set @SQL = @SQL + 'begin' + char(13);
        set @SQL = @SQL + '  create table ' + @TABLENAME + ' (STATUSMESSAGE nvarchar(1000));' + char(13);
        set @SQL = @SQL + '  insert into ' + @TABLENAME + ' ([STATUSMESSAGE]) values (''' + @STATUSMESSAGE + ''');';
        set @SQL = @SQL + 'end' + char(13);
        set @SQL = @SQL + 'else' + char(13);
        set @SQL = @SQL + '  update ' + @TABLENAME + ' set STATUSMESSAGE = ''' + @STATUSMESSAGE + '''' + char(13);

        select
          @CURRENTSTATUS = STATUSCODE 
        from
          dbo.BUSINESSPROCESSSTATUS
        where
          ID = @ID;

        if @STATUSCODE <> @CURRENTSTATUS
          begin
            if @CHANGEAGENTID is null
              exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

            set @CURRENTDATE = getdate();

            update 
              dbo.BUSINESSPROCESSSTATUS
            set
              STATUSCODE = @STATUSCODE,
              ERRORMESSAGE = @ERRORMSG,
              ENDEDON = (case when (@CURRENTSTATUS = 4 and @STATUSCODE = 1) then null else @CURRENTDATE end),-- set the ENDEDON date to the current date only if not switching from Enqueued to Running

              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CURRENTDATE,
              INNEREXCEPTION = @INNERMSG
            where
              ID = @ID;
          end

        exec sp_executesql @SQL;

      end