USP_BUSINESSPROCESSSTATUS_ADDPROCESS

      CREATE procedure [dbo].[USP_BUSINESSPROCESSSTATUS_ADDPROCESS]
          @ID uniqueidentifier,
          @BUSINESSPROCESSCATALOGID uniqueidentifier,
          @PARAMETERSETID uniqueidentifier,                                
          @STATUSTABLENAME nvarchar(120),
          @STARTEDBYUSERID uniqueidentifier, 
          @SERVERNAME    nvarchar(255),
          @CHANGEAGENTID uniqueidentifier,
          @STATUSCODE tinyint = 1,
          @ENQUEUEDJOBID bigint = 0
        with execute as caller                
        as
        begin
          set nocount on;

          declare @CURRENTDATE datetime;
          set @CURRENTDATE = getdate();

          if @ID is null
            set @ID = newid();

          if @STATUSCODE is null
            set @STATUSCODE = 1;

          if @ENQUEUEDJOBID is null
            set @ENQUEUEDJOBID = 0;

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

          declare @SQL nvarchar(500);

          -- BusinessProcessRecentStatusView checks this temp table if there is a row in the status table

          -- so we create the temp table before inserting the status row to prevent a race condition

          declare @TABLENAME nvarchar(100);
          declare @DEFAULTMESSAGE nvarchar(1000) = 'Process started...';
          if @STATUSCODE = 4
            set @DEFAULTMESSAGE = 'Process enqueued...';

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

          set @SQL = 'create table ' + @TABLENAME + '(STATUSMESSAGE nvarchar(1000))';
          exec sp_executesql @SQL;

          set @SQL = 'insert into ' + @TABLENAME + ' (STATUSMESSAGE) values (@DEFAULTMESSAGE)';
          exec sp_executesql @SQL, N'@DEFAULTMESSAGE nvarchar(1000)', @DEFAULTMESSAGE = @DEFAULTMESSAGE;

          insert into dbo.[BUSINESSPROCESSSTATUS]
            (ID, BUSINESSPROCESSCATALOGID, STATUSCODE,  STARTEDON, STARTEDBYUSERID, SERVERNAME, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BUSINESSPROCESSPARAMETERSETID, ENQUEUEDJOBID )
          values
            (@ID, @BUSINESSPROCESSCATALOGID, @STATUSCODE, GetDate(), @STARTEDBYUSERID, @SERVERNAME, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @PARAMETERSETID, @ENQUEUEDJOBID)                                    

          if len(@STATUSTABLENAME)>0
          begin
            set @SQL = 'insert into ' + @STATUSTABLENAME +
                     '(ID, PARAMETERSETID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                  values
                    (@DYNID, @DYNPARAMETERSETID, @DYNCHANGEAGENTID, @DYNCHANGEAGENTID, @DYNCURRENTDATE, @DYNCURRENTDATE)'

            exec sp_executesql @SQL, N'@DYNID uniqueidentifier, @DYNPARAMETERSETID uniqueidentifier, @DYNCHANGEAGENTID uniqueidentifier, @DYNCURRENTDATE datetime', 
                          @DYNID = @ID, @DYNPARAMETERSETID = @PARAMETERSETID, @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNCURRENTDATE = @CURRENTDATE;
          end

        end