Back to OIM Explorer

dbo.QBM_ZRecalculate

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 9; Single DBQueue insert -> select Spalte1 from ( at line 9; Bulk DBQueue insert -> select Spalte1, Spalte2 from ( at line 9; Bulk DBQueue insert -> at line 9

Source: sandbox-db sys.sql_modules

Source size: 3.900 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

  • Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 9
  • Single DBQueue insert -> select Spalte1 from ( at line 9
  • Bulk DBQueue insert -> select Spalte1, Spalte2 from ( at line 9
  • Bulk DBQueue insert -> at line 9
  • Single DBQueue insert -> select Spalte1 from ( at line 15
  • Bulk DBQueue insert -> select Spalte1, Spalte2 from ( at line 15
  • Bulk DBQueue insert -> at line 15
  • Bulk DBQueue insert -> select Spalte1, Spalte2 from ( at line 24
  • Bulk DBQueue insert -> at line 24
  • Bulk DBQueue insert -> at line 35
  • References QBM_PDBQueueInsert_Single
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QBM_ZRecalculate at line 9 Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 9
  • queues DBQueue task select Spalte1 from ( at line 9 Single DBQueue insert -> select Spalte1 from ( at line 9
  • queues DBQueue task select Spalte1, Spalte2 from ( at line 9 Bulk DBQueue insert -> select Spalte1, Spalte2 from ( at line 9
  • queues DBQueue task at line 9 Bulk DBQueue insert -> at line 9
  • queues DBQueue task select Spalte1 from ( at line 15 Single DBQueue insert -> select Spalte1 from ( at line 15
  • queues DBQueue task select Spalte1, Spalte2 from ( at line 15 Bulk DBQueue insert -> select Spalte1, Spalte2 from ( at line 15
  • queues DBQueue task at line 15 Bulk DBQueue insert -> at line 15
  • queues DBQueue task select Spalte1, Spalte2 from ( at line 24 Bulk DBQueue insert -> select Spalte1, Spalte2 from ( at line 24
  • queues DBQueue task at line 24 Bulk DBQueue insert -> at line 24
  • queues DBQueue task at line 35 Bulk DBQueue insert -> at line 35
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PDBQueueInsert_Single source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • queues DBQueue task QBM-K-CommonReCalculate -> QBM_ZRecalculate QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate', @Object, Null, @GenProcID select @ElementIndex += 1 end goto ende end select @NeuTask = @UID_Task select @SQLcmd = null if not exists (select top 1 1 from QBMDBQueueTask t with (readpast…
  • queues DBQueue task select Spalte1 from ( -> unresolved procedure QBM_PDBQueueInsert_Single @NeuTask, null, null, @GenProcID end if @countParameter = 1 begin select @SQLCmd = ' select Spalte1 from ( ' + @SQLCmd + ' ) as x (Spalte1) where x.Spalte1 > '' '' ' insert into @QBMDeltaHelper(Element) exec sp_ex…
  • queues DBQueue task select Spalte1, Spalte2 from ( -> unresolved procedure QBM_PDBQueueInsert_Bulk @NeuTask, @DBQueueElements_01 end if @countParameter = 2 begin select @SQLCmd = ' select Spalte1, Spalte2 from ( ' + @SQLCmd + ' ) as x (Spalte1, Spalte2) where x.Spalte1 > '' '' and x.Spalte2 > '' '' ' insert into …

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL193 lines
1CREATE PROCEDURE QBM_ZRecalculate(2  @SlotNumber int,3  @UID_Task varchar(38),4  @dummy varchar(38),5  @GenProcID varchar(38)6)7AS8BEGIN9  DECLARE @SQLcmd nvarchar(max)10  DECLARE @NeuTask varchar(38)11  DECLARE @CountParameter int12  DECLARE @Object varchar(38)13  DECLARE @DebugMessage nvarchar(2000)14  DECLARE @DebugLevel char(1) = 'W'15  DECLARE @ElementBuffer QBM_YCursorBuffer16  DECLARE @ElementCount int17  DECLARE @ElementIndex int18  DECLARE @QBMDeltaHelper QBM_YDBQueueDeltaHelper19  BEGIN TRY20    SELECT21      @DebugMessage = 'Recalculate betreten, Task = ' + isnull(@UID_Task,22      '<keine>')23    EXEC QBM_PJournal @DebugMessage,24      @@procid,25    'D',26      @DebugLevel27    IF isnull(@GenProcID,28    '') = ''29    BEGIN30      SELECT @GenProcID = newid()31    END32    IF @UID_Task = 'QBM-K-All'33    BEGIN34      INSERT INTO @ElementBuffer(UID1)35      SELECT36        DISTINCT t.UID_Task AS uid37      FROM QBMDBQueueTask t38      LEFT39      OUTER40      JOIN QBMDBQueueTaskMetric m41        ON t.UID_Task = m.UID_Task42      WHERE43        ((isnull(t.QueryForRecalculate, N '') > ' ' AND t.CountParameter > 0) OR t.CountParameter = 0) AND m.PathLength >(44      SELECT TOP 1 b.PathLength45      FROM QBMDBQueueTaskMetric b46      WHERE47        b.UID_Task = 'QBM-K-BaseMigrationReady')48      SELECT @ElementCount = @@ROWCOUNT49      SELECT @ElementIndex = 150      WHILE @ElementIndex <= @ElementCount51      BEGIN52        SELECT TOP 1 @Object = bu.UID153        FROM @ElementBuffer bu54        WHERE55          bu.ElementIndex = @ElementIndex56        EXEC QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate',57          @Object,58        NULL,59          @GenProcID60        SELECT @ElementIndex += 161      END62      GOTO ende63    END64    SELECT @NeuTask = @UID_Task65    SELECT @SQLcmd = NULL66    IF NOT EXISTS(67      SELECT TOP 1 168      FROM QBMDBQueueTask t69      WITH(readpast)70    WHERE71      t.UID_Task = @NeuTask)72    BEGIN73      SELECT74        @DebugMessage = 'task to recalculate not found: ' + isnull(@UID_Task,75        '<empty>')76        GOTO ende77    END78    SELECT @SQLcmd = NULL79    SELECT80      TOP 1 @SQLcmd = QueryForRecalculate,81      @CountParameter = countParameter82    FROM QBMDBQueueTask t83    WHERE84      UID_Task = @NeuTask AND((isnull(t.QueryForRecalculate, N '') > N ' ' AND t.CountParameter > 0) OR t.CountParameter = 0)85    IF @SQLcmd IS NULL AND @CountParameter > 086    BEGIN87      SELECT @DebugMessage = 'keine Query für recalculate'88      EXEC QBM_PJournal @DebugMessage,89        @@procid,90      'D',91        @DebugLevel92      GOTO ende93    END94    IF @CountParameter = 095    BEGIN96      EXEC QBM_PDBQueueInsert_Single @NeuTask,97      NULL,98      NULL,99        @GenProcID100    END101    IF @countParameter = 1102    BEGIN103      SELECT104        @SQLCmd = '105	select Spalte1 from106	( ' + @SQLCmd + '107	) as x (Spalte1)108	where x.Spalte1 > '' ''109'110      INSERT INTO @QBMDeltaHelper(Element)111      EXEC sp_executesql @SQLCmd112      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw113      INSERT INTO @DBQueueElements_01(object,114      subobject,115      genprocid)116      SELECT117        x.uid,118        NULL,119        @GenProcID120      FROM(121      SELECT122        DISTINCT Element AS uid,123        1 AS Kennung124      FROM @QBMDeltaHelper125      WHERE126        Element > ' '127      UNION all128      SELECT129        q.Object,130        2 AS Kennung131      FROM DialogDBQueue q132        WITH(nolock)133      WHERE134        q.UID_Task = @NeuTask AND q.Generation >= 0) AS x135      GROUP BY x.uid136      HAVING SUM(x.Kennung) = 1137      EXEC QBM_PDBQueueInsert_Bulk @NeuTask,138        @DBQueueElements_01139    END140    IF @countParameter = 2141    BEGIN142      SELECT143        @SQLCmd = '144	select Spalte1, Spalte2 from145	( ' + @SQLCmd + '146	) as x (Spalte1, Spalte2)147	where x.Spalte1 > '' ''148		and x.Spalte2 > '' ''149'150      INSERT INTO @QBMDeltaHelper(Element,151      AssignedElement)152      EXEC sp_executesql @SQLCmd153      DECLARE @DBQueueElements_02 QBM_YDBQueueRaw154      INSERT INTO @DBQueueElements_02(object,155      subobject,156      genprocid)157      SELECT158        x.uid,159        x.subobject,160        @GenProcID161      FROM(162      SELECT163        DISTINCT Element AS uid,164        assignedElement AS subObject,165        1 AS Kennung166      FROM @QBMDeltaHelper167      WHERE168        Element > ' ' AND assignedElement > ' '169      UNION all170      SELECT171        q.Object,172        q.SubObject,173        2 AS Kennung174      FROM DialogDBQueue q175        WITH(nolock)176      WHERE177        q.UID_Task = @NeuTask AND q.Generation >= 0) AS x178      GROUP BY x.uid,179      x.subObject180      HAVING SUM(x.Kennung) = 1181      EXEC QBM_PDBQueueInsert_Bulk @NeuTask,182        @DBQueueElements_02183    END184  END TRY185  BEGIN CATCH186    EXEC QBM_PSessionErrorAdd DEFAULT187    RAISERROR('',188    18,189    1)190      WITH NOWAIT191  END CATCH192  ende:193END
Open raw exported source
SQL · Raw37 lines
1       create   procedure QBM_ZRecalculate ( @SlotNumber int , @UID_Task varchar(38)    , @dummy varchar(38) , @GenProcID varchar(38) ) as begin2  declare @SQLcmd nvarchar(max) declare @NeuTask varchar(38) declare @CountParameter int declare @Object varchar(38) declare @DebugMessage nvarchar(20003) declare @DebugLevel char(1) = 'W' declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @QBMDeltaHelper4 QBM_YDBQueueDeltaHelper BEGIN TRY select @DebugMessage = 'Recalculate betreten, Task = ' + isnull(@UID_Task, '<keine>') exec QBM_PJournal @DebugMessage5 , @@procid, 'D', @DebugLevel if isnull(@GenProcID, '') = '' begin select @GenProcID = newid() end if @UID_Task = 'QBM-K-All' begin  insert into @ElementBuffer6 (UID1) select distinct t.UID_Task as uid from QBMDBQueueTask t left outer join QBMDBQueueTaskMetric m on t.UID_Task = m.UID_Task where ( (isnull(t.QueryForRecalculate7, N'') > ' ' and t.CountParameter > 0) or t.CountParameter = 0 )       and m.PathLength > (select top 1 b.PathLength from QBMDBQueueTaskMetric b where 8b.UID_Task = 'QBM-K-BaseMigrationReady' ) select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select 9top 1 @Object = bu.UID1 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex exec QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate', @Object,10 Null, @GenProcID select @ElementIndex += 1 end  goto ende end   select @NeuTask = @UID_Task select @SQLcmd = null  if not exists (select top 1 1 from 11QBMDBQueueTask t with (readpast) where t.UID_Task = @NeuTask ) begin  select @DebugMessage = 'task to recalculate not found: ' + isnull(@UID_Task, '<empty>'12)    goto ende end select @SQLcmd = null select top 1 @SQLcmd = QueryForRecalculate , @CountParameter = countParameter from QBMDBQueueTask t where UID_Task13 = @NeuTask and ( (isnull(t.QueryForRecalculate, N'') > N' ' and t.CountParameter > 0) or t.CountParameter = 0 ) if @SQLcmd is null   and @CountParameter14 > 0 begin select @DebugMessage = 'keine Query für recalculate' exec QBM_PJournal @DebugMessage , @@procid, 'D', @DebugLevel goto ende end  if @CountParameter15 = 0 begin exec QBM_PDBQueueInsert_Single @NeuTask, null, null, @GenProcID end if @countParameter = 1 begin   select @SQLCmd = '16	select Spalte1 from17	( '18 + @SQLCmd + '19	) as x (Spalte1)20	where x.Spalte1 > '' ''21' insert into @QBMDeltaHelper(Element) exec sp_executesql @SQLCmd  declare @DBQueueElements_0122 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select distinct Element as uid, 123 as Kennung from @QBMDeltaHelper where Element > ' ' union all  select q.Object, 2 as Kennung from DialogDBQueue q with (nolock) where q.UID_Task = @NeuTask24 and q.Generation >= 0 ) as x group by x.uid having SUM(x.Kennung) = 1        exec QBM_PDBQueueInsert_Bulk @NeuTask, @DBQueueElements_01 end if @countParameter25 = 2 begin   select @SQLCmd = '26	select Spalte1, Spalte2 from27	( ' + @SQLCmd + '28	) as x (Spalte1, Spalte2)29	where x.Spalte1 > '' ''30		and x.Spalte2 > '' ''31'32 insert into @QBMDeltaHelper (Element, AssignedElement) exec sp_executesql @SQLCmd  declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_0233 (object, subobject, genprocid) select x.uid, x.subobject, @GenProcID from ( select distinct Element as uid, assignedElement as subObject, 1 as Kennung34 from @QBMDeltaHelper where Element > ' ' and assignedElement > ' ' union all  select q.Object, q.SubObject, 2 as Kennung from DialogDBQueue q with (nolock35) where q.UID_Task = @NeuTask and q.Generation >= 0 ) as x group by x.uid, x.subObject having SUM(x.Kennung) = 1         exec QBM_PDBQueueInsert_Bulk @NeuTask36, @DBQueueElements_02 end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende: end 37