Back to OIM Explorer

dbo.QER_ZOrgTreeLevel

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> QER-K-OrgTreeLEVEL / QER_ZOrgTreeLevel at line 8; Bulk DBQueue insert -> QER-K-OrgTreeLEVEL / QER_ZOrgTreeLevel at line 8; Bulk DBQueue insert -> QER-K-OrgTreeLEVEL / QER_ZOrgTreeLevel at line 16; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 2.812 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 -> QER-K-OrgTreeLEVEL / QER_ZOrgTreeLevel at line 8
  • Bulk DBQueue insert -> QER-K-OrgTreeLEVEL / QER_ZOrgTreeLevel at line 8
  • Bulk DBQueue insert -> QER-K-OrgTreeLEVEL / QER_ZOrgTreeLevel at line 16
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QER_ZOrgTreeLevel at line 8 Bulk DBQueue insert -> QER-K-OrgTreeLEVEL / QER_ZOrgTreeLevel at line 8
  • queues DBQueue task QER_ZOrgTreeLevel at line 16 Bulk DBQueue insert -> QER-K-OrgTreeLEVEL / QER_ZOrgTreeLevel at line 16
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • queues DBQueue task QER-K-OrgTreeLEVEL -> QER_ZOrgTreeLevel QBM_PDBQueueInsert_Bulk 'QER-K-OrgTreeLEVEL', @DBQueueElements_01 update BaseTree set treelevel = 0 , XDateUpdated = @Xdate , XUserUpdated = @XUser where exists (select top 1 1 from @QBMDeltaHelper v where convert(int, v.AssignedElement) <…

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL133 lines
1CREATE PROCEDURE QER_ZOrgTreeLevel(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @CountItems int7  DECLARE @QBMDeltaHelper QBM_YDBQueueDeltaHelper8  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent9  DECLARE @XUser nvarchar(64) = object_name(@@procid)10  DECLARE @Xdate datetime = getutcdate()11  BEGIN TRY12    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,13    UID_Parameter,14    UID_SubParameter,15    GenProcID)16    SELECT17      UID_DialogDBQueue,18      UID_Parameter,19      UID_SubParameter,20      GenProcID21    FROM QBMDBQueueCurrent cu22      WITH(readpast)23    WHERE24      cu.SlotNumber = @SlotNumber25    IF @@rowcount = 026    BEGIN27      GOTO EndLabel28    END29    INSERT INTO @QBMDeltaHelper(Element,30    AssignedElement,31    XOrigin)32    SELECT33      b.uid_org,34      convert(varchar(38),35      isnull(b.treelevel, -1)),36      037    FROM @DBQueueCurrent p38    JOIN BaseTree b39      ON b.uid_org = p.uid_parameter40    WHERE41      b.uid_parentorg IS NULL42    SELECT @CountItems = @@rowcount43    IF @CountItems > 044    BEGIN45      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw46      INSERT INTO @DBQueueElements_01(object,47      subobject,48      genprocid)49      SELECT50        x.uid,51        NULL,52        x.GenProcID53      FROM(54      SELECT55        b.uid_org AS uid,56        p.genprocid57      FROM BaseTree b58      JOIN @DBQueueCurrent p59        ON b.uid_parentorg = p.uid_parameter60      JOIN @QBMDeltaHelper v61        ON v.Element = p.uid_parameter AND convert(int, v.AssignedElement) <> v.XOrigin) AS x62      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-OrgTreeLEVEL',63        @DBQueueElements_0164      UPDATE BaseTree65      SET treelevel = 0,66      XDateUpdated = @Xdate,67      XUserUpdated = @XUser68      WHERE69        EXISTS(70      SELECT TOP 1 171      FROM @QBMDeltaHelper v72      WHERE73        convert(int, v.AssignedElement) <> v.XOrigin AND BaseTree.uid_org = v.Element)74    END75    INSERT INTO @QBMDeltaHelper(Element,76    AssignedElement,77    XOrigin)78    SELECT79      b.uid_org,80      convert(varchar(38),81      isnull(b.treelevel, -3)),82      isnull(pa.treelevel,83      -2) +184    FROM @DBQueueCurrent p85    JOIN BaseTree b86      ON b.uid_org = p.uid_parameter87    JOIN BaseTree pa88      ON b.uid_parentorg = pa.uid_org89    DECLARE @DBQueueElements_02 QBM_YDBQueueRaw90    INSERT INTO @DBQueueElements_02(object,91    subobject,92    genprocid)93    SELECT94      x.uid,95      NULL,96      x.GenProcID97    FROM(98    SELECT99      b.uid_org AS uid,100      p.genprocid101    FROM BaseTree b102    JOIN @DBQueueCurrent p103      ON b.uid_parentorg = p.uid_parameter104    JOIN @QBMDeltaHelper v105      ON v.Element = p.uid_parameter AND convert(int, v.AssignedElement) <> v.XOrigin AND v.XOrigin <> 0106    UNION107    SELECT108      p.uid_parameter,109      p.genprocid110    FROM @QBMDeltaHelper v111    JOIN @DBQueueCurrent p112      ON v.Element = p.uid_parameter AND convert(int, v.AssignedElement) = -3) AS x113    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-OrgTreeLEVEL',114      @DBQueueElements_02115    UPDATE BaseTree116    SET treelevel = v.XOrigin,117    XDateUpdated = @Xdate,118    XUserUpdated = @XUser119    FROM BaseTree,120    @QBMDeltaHelper v121    WHERE122      v.Element = BaseTree.uid_org AND convert(int,123    v.AssignedElement) <> v.XOrigin AND v.XOrigin >= 0124  END TRY125  BEGIN CATCH126    EXEC QBM_PSessionErrorAdd DEFAULT127    RAISERROR('',128    18,129    1)130      WITH NOWAIT131  END CATCH132  endLabel:133END
Open raw exported source
SQL · Raw19 lines
1       create   procedure QER_ZOrgTreeLevel (@SlotNumber int) AS begin declare @CountItems int    declare @QBMDeltaHelper QBM_YDBQueueDeltaHelper2 declare @DBQueueCurrent QBM_YDBQueueCurrent declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() BEGIN TRY insert3 into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID4 from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end   insert into @QBMDeltaHelper(Element5, AssignedElement, XOrigin) select b.uid_org, convert(varchar(38), isnull(b.treelevel, -1)) ,0 from @DBQueueCurrent p join BaseTree b on b.uid_org = p.uid_parameter6 where b.uid_parentorg is null select @CountItems = @@rowcount if @CountItems > 0 begin  declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_017 (object, subobject, genprocid) select x.uid, null, x.GenProcID  from ( select b.uid_org as uid, p.genprocid from BaseTree b join @DBQueueCurrent p on 8b.uid_parentorg = p.uid_parameter  join @QBMDeltaHelper v on v.Element = p.uid_parameter and convert(int, v.AssignedElement) <> v.XOrigin ) as x exec QBM_PDBQueueInsert_Bulk9 'QER-K-OrgTreeLEVEL', @DBQueueElements_01  update BaseTree set treelevel = 0   , XDateUpdated = @Xdate , XUserUpdated = @XUser where exists (select top10 1 1 from @QBMDeltaHelper v where convert(int, v.AssignedElement) <> v.XOrigin and BaseTree.uid_org = v.Element ) end   insert into @QBMDeltaHelper(Element11, AssignedElement, XOrigin) select b.uid_org, convert(varchar(38), isnull(b.treelevel, -3)) , isnull(pa.treelevel, -2) +1 from @DBQueueCurrent p join BaseTree12 b on b.uid_org = p.uid_parameter join BaseTree pa on b.uid_parentorg = pa.uid_org  declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_0213 (object, subobject, genprocid) select x.uid, null, x.GenProcID from ( select b.uid_org as uid, p.genprocid from BaseTree b join @DBQueueCurrent p on b.uid_parentorg14 = p.uid_parameter  join @QBMDeltaHelper v on v.Element = p.uid_parameter and convert(int, v.AssignedElement) <> v.XOrigin  and v.XOrigin <> 0  union select15 p.uid_parameter, p.genprocid  from @QBMDeltaHelper v join @DBQueueCurrent p on v.Element = p.uid_parameter and convert(int, v.AssignedElement) = -3 ) 16as x exec QBM_PDBQueueInsert_Bulk 'QER-K-OrgTreeLEVEL', @DBQueueElements_02  update BaseTree set treelevel = v.XOrigin   , XDateUpdated = @Xdate , XUserUpdated17 = @XUser from BaseTree, @QBMDeltaHelper v where v.Element = BaseTree.uid_org and convert(int, v.AssignedElement) <> v.XOrigin     and v.XOrigin >= 0  18END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: end 19