Back to OIM Explorer

dbo.QBM_ZProcessChainStateUpdate

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> QBM-K-CommonProcess / QBM_ZProcessStateUpdate at line 11; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 1.796 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Bulk DBQueue insert -> QBM-K-CommonProcess / QBM_ZProcessStateUpdate at line 11
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QBM_ZProcessStateUpdate at line 11 Bulk DBQueue insert -> QBM-K-CommonProcess / QBM_ZProcessStateUpdate at line 11
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • queues DBQueue task QBM-K-CommonProcess -> QBM_ZProcessStateUpdate QBM_PDBQueueInsert_Bulk 'QBM-K-CommonProcess', @DBQueueElements_01 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende: end

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL88 lines
1CREATE PROCEDURE QBM_ZProcessChainStateUpdate(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @XUser nvarchar(64) = object_name(@@procid)7  DECLARE @Xdate datetime = getutcdate()8  BEGIN TRY9    DECLARE @Hilfstabelle QBM_YMNTable10    INSERT INTO @Hilfstabelle(UID_Element1,11    UID_Element2)12    SELECT13      y.uid_parameter,14      w.ChainState15    FROM(16    SELECT17      p.uid_parameter,18      sum(DISTINCT x.StateLevel) AS StateLevel19    FROM QBMDBQueueCurrent p20      WITH(readpast)21    JOIN DialogProcessChain c22      ON p.uid_parameter = c.uid_tree23    LEFT24    OUTER25    JOIN DialogProcessStep s26      ON c.uid_tree = s.uid_tree27    LEFT28    OUTER29    JOIN(30    VALUES('F', 4),('E', 8),('L', 1),('D', 1),('P', 1),('G', 2),('N', 0),('#', 8)) AS x(JobState, StateLevel)31      ON isnull(s.ProcessState, '#') = x.JobState32    WHERE33      p.SlotNumber = @SlotNumber34    GROUP BY p.uid_parameter) AS y35    JOIN(36    VALUES(1, 'P'),37    (2, 'G'),38    (3, 'P'),39    (4, 'F'),40    (5, 'P'),41    (6, 'P'),42    (7, 'P'),43    (8, 'E'),44    (9, 'P'),45    (10, 'P'),46    (11, 'P'),47    (12, 'F'),48    (13, 'P'),49    (14, 'P'),50    (15, 'P'),51    (0, 'G')) AS w(StateLevel,52    ChainState)53      ON y.StateLevel = w.StateLevel54    UPDATE DialogProcessChain55    SET ProcessState = w.UID_Element2,56    XDateUpdated = @Xdate,57    XUserUpdated = @XUser58    FROM DialogProcessChain,59    @Hilfstabelle w60    WHERE61      DialogProcessChain.uid_tree = w.UID_Element1 AND isnull(DialogProcessChain.ProcessState,62    '') <> w.UID_Element263    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw64    INSERT INTO @DBQueueElements_01(object,65    subobject,66    genprocid)67    SELECT68      x.uid,69      NULL,70      x.uid71    FROM(72    SELECT73      DISTINCT c.GenProcID AS uid74    FROM @Hilfstabelle s175    JOIN DialogProcessChain c76      ON s1.UID_Element1 =c.uid_Tree) AS x77    EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonProcess',78      @DBQueueElements_0179  END TRY80  BEGIN CATCH81    EXEC QBM_PSessionErrorAdd DEFAULT82    RAISERROR('',83    18,84    1)85      WITH NOWAIT86  END CATCH87  ende:88END
Open raw exported source
SQL ยท Raw13 lines
1       create   procedure QBM_ZProcessChainStateUpdate (@SlotNumber int)  AS begin  declare @XUser nvarchar(64) = object_name(@@procid) declare 2@Xdate datetime = getutcdate() BEGIN TRY   declare @Hilfstabelle QBM_YMNTable  insert into @Hilfstabelle (UID_Element1 , UID_Element2 ) select y.uid_parameter3 , w.ChainState from ( select p.uid_parameter, sum(distinct x.StateLevel) as StateLevel  from QBMDBQueueCurrent p with (readpast) join DialogProcessChain4 c on p.uid_parameter = c.uid_tree left outer join DialogProcessStep s on c.uid_tree = s.uid_tree     left outer join ( values ('F', 4)  ,( 'E', 8) ,('L'5, 1) ,('D', 1) ,('P', 1) ,('G', 2) ,('N', 0) ,('#', 8)  ) as x (JobState, StateLevel) on isnull(s.ProcessState, '#') = x.JobState where p.SlotNumber = 6@SlotNumber group by p.uid_parameter ) as y join ( values ( 1, 'P')  ,( 2, 'G') ,( 3, 'P') ,( 4, 'F') ,( 5, 'P') ,( 6, 'P') ,( 7, 'P') ,( 8, 'E') ,( 9,7 'P') ,(10, 'P') ,(11, 'P') ,(12, 'F') ,(13, 'P') ,(14, 'P') ,(15, 'P') ,( 0, 'G') ) as w (StateLevel , ChainState ) on y.StateLevel = w.StateLevel  update8 DialogProcessChain set ProcessState = w.UID_Element2   , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogProcessChain, @Hilfstabelle w where 9DialogProcessChain.uid_tree = w.UID_Element1  and isnull(DialogProcessChain.ProcessState,'') <> w.UID_Element2   declare @DBQueueElements_01 QBM_YDBQueueRaw10 insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, x.uid  from ( select distinct c.GenProcID as uid from @Hilfstabelle11 s1 join DialogProcessChain c on s1.UID_Element1  =c.uid_Tree ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonProcess', @DBQueueElements_01 END TRY BEGIN12 CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende: end 13