Back to OIM Explorer

dbo.QBM_ZPrePropTree

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> QBM-K-CommonPrepropSheet / QBM_ZPrePropSheet at line 19; Bulk DBQueue insert -> QBM-K-CommonPrepropTree / QBM_ZPrePropTree at line 19; Bulk DBQueue insert -> QBM-K-CommonPrepropTree / QBM_ZPrePropTree at line 26; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 4.194 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-CommonPrepropSheet / QBM_ZPrePropSheet at line 19
  • Bulk DBQueue insert -> QBM-K-CommonPrepropTree / QBM_ZPrePropTree at line 19
  • Bulk DBQueue insert -> QBM-K-CommonPrepropTree / QBM_ZPrePropTree at line 26
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QBM_ZPrePropSheet at line 19 Bulk DBQueue insert -> QBM-K-CommonPrepropSheet / QBM_ZPrePropSheet at line 19
  • queues DBQueue task QBM_ZPrePropTree at line 19 Bulk DBQueue insert -> QBM-K-CommonPrepropTree / QBM_ZPrePropTree at line 19
  • queues DBQueue task QBM_ZPrePropTree at line 26 Bulk DBQueue insert -> QBM-K-CommonPrepropTree / QBM_ZPrePropTree at line 26
  • references source dbo.QBM_FGIPrepropConditionDeactiv source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PDBQueueInsert_WaitForComp source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • queues DBQueue task QBM-K-CommonPrepropSheet -> QBM_ZPrePropSheet QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropSheet', @DBQueueElements_01 exec QBM_PSessionContextSet 'GenProcID', @GenProcID update QBMTree set IsDeactivatedByPreProcessor = x.isDeactivated, xdateupdated = GetUTCDate(), xuserupdated = 'DBSc…
  • queues DBQueue task QBM-K-CommonPrepropTree -> QBM_ZPrePropTree QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTree', @DBQueueElements_02 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18 , 1) WITH NOWAIT END CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R retur…

References

Referenced By

Complete Source

SQL174 lines
1CREATE PROCEDURE QBM_ZPrePropTree(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @CountItems int7  DECLARE @GenProcID varchar(38)8  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')9  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent10  BEGIN TRY11    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,12    UID_Parameter,13    UID_SubParameter,14    GenProcID)15    SELECT16      UID_DialogDBQueue,17      UID_Parameter,18      UID_SubParameter,19      GenProcID20    FROM QBMDBQueueCurrent cu21      WITH(readpast)22    WHERE23      cu.SlotNumber = @SlotNumber24    IF @@rowcount = 025    BEGIN26      GOTO EndLabel27    END28    SELECT @GenProcID = max(GenProcID)29    FROM @DBQueueCurrent30    WHERE31      GenProcID > ' '32    IF isnull(@Genprocid,33    '') = ''34    BEGIN35      SELECT @GenProcID = newid()36    END37    DECLARE @PrePropHelper QBM_YPrepropHelper38    INSERT INTO @PrePropHelper(uid_parameter,39    isDeactivatedPrior,40    IsDeactivated,41    GenProcID,42    PreProcessorCondition,43    IsDeactivatedOld)44    SELECT45      x.uid_parameter,46      sign(cast(isnull(p.isDeactivatedByPreProcessor, 0) AS int) + cast(isnull(tb.isDeactivatedByPreProcessor,47      0) AS int) + cast(isnull(dd.isDeactivatedByPreProcessor, 0) AS int)),48      sign(cast(isnull(p.isDeactivatedByPreProcessor, 0) AS int) + cast(isnull(tb.isDeactivatedByPreProcessor,49      0) AS int) + cast(isnull(dd.IsDeactivatedByPreProcessor, 0) AS int)),50      x.GenProcID,51      isnull(t.PreProcessorCondition,52      N ''),53      t.isDeactivatedByPreProcessor54    FROM @DBQueueCurrent x55    JOIN QBMTree t56      ON x.uid_parameter = t.uid_QBMTree57    LEFT58    OUTER59    JOIN QBMTree p60      ON t.UID_QBMTreeParent = p.UID_QBMTree61    LEFT62    OUTER63    JOIN DialogTable tb64      ON t.UID_DialogTable = tb.UID_DialogTable65    LEFT66    OUTER67    JOIN DialogDashBoardDef dd68      ON t.UID_DialogDashBoardDef = dd.UID_DialogDashBoardDef69    UPDATE @PrePropHelper70    SET IsDeactivated = dbo.QBM_FGIPrepropConditionDeactiv(h.PreProcessorCondition)71    FROM @PrePropHelper h72    WHERE73      h.PreProcessorCondition > ' ' AND h.isDeactivatedPrior = 074    UPDATE @PrePropHelper75    SET IsDeactivated = 176    FROM @PrePropHelper h77    JOIN QBMTree t78      ON h.UID_Parameter = t.UID_QBMTree79    WHERE80      h.IsDeactivated = 0 AND t.NodeType <> 'D' AND NOT EXISTS(81    SELECT TOP 1 182    FROM QBMTreeResult r83    JOIN QBMTreeHasTreeResult thr84      ON r.UID_QBMTreeResult = thr.UID_QBMTreeResult85    WHERE86      thr.UID_QBMTree = h.UID_Parameter AND r.isDeactivatedByPreProcessor = 0) AND EXISTS(87    SELECT TOP 1 188    FROM QBMTreeResult r89    JOIN QBMTreeHasTreeResult thr90      ON r.UID_QBMTreeResult = thr.UID_QBMTreeResult91    WHERE92      thr.UID_QBMTree = h.UID_Parameter) AND NOT EXISTS(93    SELECT TOP 1 194    FROM QBMTree c95    WHERE96      c.UID_QBMTreeParent = h.uid_parameter AND c.IsDeactivatedByPreProcessor = 0)97    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw98    INSERT INTO @DBQueueElements_01(object,99    subobject,100    genprocid)101    SELECT102      x.uid,103      NULL,104      @GenProcID105    FROM(106    SELECT107      DISTINCT ths.UID_DialogSheet AS UID,108      x.GenProcID109    FROM QBMTree t110    JOIN @PrePropHelper x111      ON isnull(t.UID_QBMTree, '') = x.uid_parameter AND isnull(t.IsDeactivatedByPreProcessor, 0) <> x.IsDeactivated112    JOIN QBMTreeHasSheet ths113      ON ths.uid_QBMTree = t.uid_QBMTree) AS x114    EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropSheet',115      @DBQueueElements_01116    EXEC QBM_PSessionContextSet 'GenProcID',117      @GenProcID118    UPDATE QBMTree119    SET IsDeactivatedByPreProcessor = x.isDeactivated,120    xdateupdated = GetUTCDate(),121    xuserupdated = 'DBScheduler'122    FROM QBMTree r123    JOIN @PrePropHelper x124      ON r.UID_QBMTree = x.uid_parameter125    WHERE126      r.IsDeactivatedByPreProcessor <> x.IsDeactivated127    SELECT @CountItems = @@rowcount128    IF @CountItems > 0129    BEGIN130      EXEC QBM_PDBQueueInsert_WaitForComp 'Tree',131        @GenProcID132    END133    DECLARE @DBQueueElements_02 QBM_YDBQueueRaw134    INSERT INTO @DBQueueElements_02(object,135    subobject,136    genprocid)137    SELECT138      z.UID,139      NULL,140      z.GenProcID141    FROM(142    SELECT143      t.UID_QBMTree AS UID,144      x.GenProcID,145      x.isDeactivated,146      x.isDeactivatedOld147    FROM QBMTree t148    JOIN @PrePropHelper x149      ON t.UID_QBMTreeParent = x.uid_parameter150    GROUP BY t.UID_QBMTree,151    x.GenProcID,152    x.isDeactivated,153    x.isDeactivatedOld) AS z154    WHERE155      (NOT EXISTS(156    SELECT TOP 1 1157    FROM @PrePropHelper y158    WHERE159      y.uid_parameter = z.UID) OR z.isDeactivated <> z.isDeactivatedOld)160    EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTree',161      @DBQueueElements_02162  END TRY163  BEGIN CATCH164    EXEC QBM_PSessionErrorAdd DEFAULT165    RAISERROR('',166    18,167    1)168      WITH NOWAIT169  END CATCH170  endLabel:171  EXEC QBM_PSessionContextSet 'GenProcID',172    @GenProcID_R173  RETURN174END
Open raw exported source
SQL · Raw28 lines
1   create   procedure QBM_ZPrePropTree (@SlotNumber int) as begin declare @CountItems int  declare @GenProcID varchar(38) declare @GenProcID_R varchar2(38) = dbo.QBM_FGISessionContext('') declare @DBQueueCurrent QBM_YDBQueueCurrent BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter3, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber4 = @SlotNumber if @@rowcount = 0 begin goto EndLabel end select @GenProcID = max(GenProcID) from @DBQueueCurrent where GenProcID > ' ' if isnull(@Genprocid5, '') = '' begin select @GenProcID = newid() end declare @PrePropHelper QBM_YPrepropHelper insert into @PrePropHelper (uid_parameter , isDeactivatedPrior6 , IsDeactivated , GenProcID , PreProcessorCondition, IsDeactivatedOld) select x.uid_parameter , sign(cast(isnull(p.isDeactivatedByPreProcessor,0)as int7) + cast(isnull(tb.isDeactivatedByPreProcessor,0)as int) + cast(isnull(dd.isDeactivatedByPreProcessor,0)as int) )  , sign(cast(isnull(p.isDeactivatedByPreProcessor8,0)as int) + cast(isnull(tb.isDeactivatedByPreProcessor,0)as int) + cast(isnull(dd.IsDeactivatedByPreProcessor,0)as int) ),  x.GenProcID, isnull(t.PreProcessorCondition9,N''), t.isDeactivatedByPreProcessor from @DBQueueCurrent x join QBMTree t on x.uid_parameter = t.uid_QBMTree left outer join QBMTree p on t.UID_QBMTreeParent10 = p.UID_QBMTree  left outer join DialogTable tb on t.UID_DialogTable = tb.UID_DialogTable left outer join DialogDashBoardDef dd on t.UID_DialogDashBoardDef11 = dd.UID_DialogDashBoardDef  update @PrePropHelper set IsDeactivated = dbo.QBM_FGIPrepropConditionDeactiv(h.PreProcessorCondition) from @PrePropHelper12 h where h.PreProcessorCondition > ' ' and h.isDeactivatedPrior = 0  update @PrePropHelper set IsDeactivated = 1 from @PrePropHelper h join QBMTree t on13 h.UID_Parameter = t.UID_QBMTree where h.IsDeactivated = 0 and t.NodeType <> 'D' and not exists (select top 1 1 from QBMTreeResult r join QBMTreeHasTreeResult14 thr on r.UID_QBMTreeResult = thr.UID_QBMTreeResult where thr.UID_QBMTree = h.UID_Parameter and r.isDeactivatedByPreProcessor = 0 ) and exists (select 15top 1 1 from QBMTreeResult r join QBMTreeHasTreeResult thr on r.UID_QBMTreeResult = thr.UID_QBMTreeResult where thr.UID_QBMTree = h.UID_Parameter ) and16 not exists (select top 1 1 from QBMTree c where c.UID_QBMTreeParent = h.uid_parameter and c.IsDeactivatedByPreProcessor = 0 )    declare @DBQueueElements_0117 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select distinct ths.UID_DialogSheet18 as UID, x.GenProcID from QBMTree t join @PrePropHelper x on isnull(t.UID_QBMTree,'') = x.uid_parameter and isnull(t.IsDeactivatedByPreProcessor,0) <> 19x.IsDeactivated join QBMTreeHasSheet ths on ths.uid_QBMTree = t.uid_QBMTree ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropSheet', @DBQueueElements_0120  exec QBM_PSessionContextSet 'GenProcID', @GenProcID update QBMTree set IsDeactivatedByPreProcessor = x.isDeactivated, xdateupdated = GetUTCDate(), xuserupdated21 = 'DBScheduler'  from QBMTree r join @PrePropHelper x on r.UID_QBMTree = x.uid_parameter where r.IsDeactivatedByPreProcessor <> x.IsDeactivated select22 @CountItems = @@rowcount if @CountItems > 0 begin   exec QBM_PDBQueueInsert_WaitForComp 'Tree', @GenProcID end   declare @DBQueueElements_02 QBM_YDBQueueRaw23 insert into @DBQueueElements_02 (object, subobject, genprocid) select z.UID, null, z.GenProcID from (select t.UID_QBMTree as UID, x.GenProcID, x.isDeactivated24, x.isDeactivatedOld from QBMTree t join @PrePropHelper x on t.UID_QBMTreeParent = x.uid_parameter  group by t.UID_QBMTree, x.GenProcID, x.isDeactivated25, x.isDeactivatedOld ) as z  where (not exists (select top 1 1 from @PrePropHelper y where y.uid_parameter = z.UID ) or z.isDeactivated <> z.isDeactivatedOld26 ) exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTree', @DBQueueElements_02  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 1827, 1) WITH NOWAIT END CATCH  endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R return end 28