Back to OIM Explorer

dbo.QBM_PDBQueuePondEnd

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.529 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVDatetimeToString source text reference
  • references source dbo.QBM_FGITableCountAll source text reference
  • references source dbo.QBM_P40B0C8AE71E45BA9C883A96_ source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL117 lines
1CREATE PROCEDURE QBM_PDBQueuePondEnd(2  @PondGroup varchar(38)3)4AS5BEGIN6  DECLARE @TaskDefs QBM_YCursorBuffer7  DECLARE @ElementCount int8  DECLARE @ElementIndex int9  DECLARE @UID_Task varchar(38)10  DECLARE @PondGroupNew varchar(38) = @PondGroup11  DECLARE @AnzahlPondGesamt bigint = dbo.QBM_FGITableCountAll('QBMDBQueuePond')12  DECLARE @DebugSwitch int = 013  SET XACT_ABORT OFF14  BEGIN TRY15    IF @DebugSwitch > 016    BEGIN17      print dbo.QBM_FCVDatetimeToString(GETUTCDATE()) + ' Anfang '18    END19    IF @AnzahlPondGesamt > 2500000 OR EXISTS(20      SELECT TOP 1 121      FROM(22      SELECT23        p.PondGroup, COUNT(*) AS Anzahl, COUNT(DISTINCT checksum(p.UID_Task, isnull(p.Object, ''), isnull(p.SubObject,24    ''))) AS AnzahlDistinct25    FROM QBMDBQueuePond p26    WHERE27      p.PondGroup = @PondGroup28    GROUP BY p.PondGroup) AS x29    WHERE30      x.AnzahlDistinct * 5 < x.Anzahl OR x.Anzahl > 500000)31    BEGIN32      IF @DebugSwitch > 033      BEGIN34        print 'wir verdichten'35      END36      SELECT @PondGroupNew = NEWID()37      INSERT INTO QBMDBQueuePond(UID_Task,38      Object,39      SubObject,40      GenProcID,41      PondGroup,42      InsertDate)43      SELECT44        UID_Task,45        Object,46        SubObject,47        max(GenProcID) AS GenProcID,48        @PondGroupNew AS PondGroup,49        MAX(InsertDate) AS InsertDate50      FROM QBMDBQueuePond p51      WHERE52        p.PondGroup = @PondGroup53      GROUP BY p.UID_Task,54      p.Object,55      p.SubObject56    END57    INSERT INTO @TaskDefs(UID1)58    SELECT x.UID_Task59    FROM(60    SELECT61      DISTINCT t.UID_Task62    FROM QBMDBQueuePond t63    WHERE64      t.PondGroup = @PondGroupNew) AS x65    JOIN QBMDBQueueTaskMetric mo66      ON x.UID_Task = mo.UID_Task67    ORDER BY mo.PathLength DESC68    SELECT @ElementCount = @@ROWCOUNT69    SELECT @ElementIndex = 170    WHILE @ElementIndex <= @ElementCount71    BEGIN72      SELECT TOP 1 @UID_Task = bu.UID173      FROM @TaskDefs bu74      WHERE75        bu.ElementIndex = @ElementIndex76      IF @DebugSwitch > 077      BEGIN78        print dbo.QBM_FCVDatetimeToString(GETUTCDATE()) + ' Task ' + @UID_Task79      END80      EXEC QBM_P40B0C8AE71E45BA9C883A96_ @PondGroupNew,81        @UID_Task82      SELECT @ElementIndex += 183    END84    IF @DebugSwitch > 085    BEGIN86      print dbo.QBM_FCVDatetimeToString(GETUTCDATE()) + ' Kennzeichnen ' + @PondGroupNew87    END88    UPDATE QBMDBQueuePond89    SET GenProcID = 'DELETE'90    WHERE91      PondGroup = @PondGroupNew92    IF @PondGroup <> @PondGroupNew93    BEGIN94      IF @DebugSwitch > 095      BEGIN96        print dbo.QBM_FCVDatetimeToString(GETUTCDATE()) + ' Kennzeichnen ' + @PondGroup97      END98      UPDATE QBMDBQueuePond99      SET GenProcID = 'DELETE'100      WHERE101        PondGroup = @PondGroup102    END103  END TRY104  BEGIN CATCH105    EXEC QBM_PSessionErrorAdd DEFAULT106    RAISERROR('',107    18,108    1)109      WITH NOWAIT110  END CATCH111  endLabel:112  IF @DebugSwitch > 0113  BEGIN114    print dbo.QBM_FCVDatetimeToString(GETUTCDATE()) + ' ferdsch '115  END116  RETURN117END
Open raw exported source
SQL ยท Raw18 lines
1 create   procedure QBM_PDBQueuePondEnd (@PondGroup varchar(38) ) as begin declare @TaskDefs QBM_YCursorBuffer declare @ElementCount int declare2 @ElementIndex int declare @UID_Task varchar(38)    declare @PondGroupNew varchar(38) = @PondGroup declare @AnzahlPondGesamt bigint = dbo.QBM_FGITableCountAll3('QBMDBQueuePond') declare @DebugSwitch int = 0 SET XACT_ABORT OFF BEGIN TRY     if @DebugSwitch > 0 begin print dbo.QBM_FCVDatetimeToString(GETUTCDATE4()) + ' Anfang ' end if @AnzahlPondGesamt > 2500000 or exists ( select top 1 1 from ( select p.PondGroup, COUNT(*) as Anzahl, COUNT(distinct checksum(p.UID_Task5 , isnull(p.Object, '') , isnull(p.SubObject, ''))) as AnzahlDistinct from QBMDBQueuePond p where p.PondGroup = @PondGroup  group by p.PondGroup ) as x6 where x.AnzahlDistinct * 5 < x.Anzahl or x.Anzahl > 500000 ) begin if @DebugSwitch > 0 begin print 'wir verdichten' end select @PondGroupNew = NEWID()7  insert into QBMDBQueuePond(UID_Task, Object, SubObject, GenProcID  , PondGroup, InsertDate) select UID_Task, Object , SubObject , max(GenProcID) as GenProcID8  , @PondGroupNew as PondGroup, MAX(InsertDate) as InsertDate from QBMDBQueuePond p where p.PondGroup = @PondGroup  group by p.UID_Task, p.Object , p.SubObject9 end insert into @TaskDefs (UID1) select x.UID_Task from ( select distinct t.UID_Task from QBMDBQueuePond t where t.PondGroup = @PondGroupNew  ) as x join10 QBMDBQueueTaskMetric mo on x.UID_Task = mo.UID_Task order by mo.PathLength desc select @ElementCount = @@ROWCOUNT select @ElementIndex = 1     while @ElementIndex11 <= @ElementCount begin select top 1 @UID_Task = bu.UID1       from @TaskDefs bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print 12dbo.QBM_FCVDatetimeToString(GETUTCDATE()) + ' Task ' + @UID_Task end exec QBM_P40B0C8AE71E45BA9C883A96_ @PondGroupNew , @UID_Task select @ElementIndex 13+= 1 end     if @DebugSwitch > 0 begin print dbo.QBM_FCVDatetimeToString(GETUTCDATE()) + ' Kennzeichnen ' + @PondGroupNew end update QBMDBQueuePond set14 GenProcID = 'DELETE' where PondGroup = @PondGroupNew if @PondGroup <> @PondGroupNew begin if @DebugSwitch > 0 begin print dbo.QBM_FCVDatetimeToString(GETUTCDATE15()) + ' Kennzeichnen ' + @PondGroup end update QBMDBQueuePond set GenProcID = 'DELETE' where PondGroup = @PondGroup end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd16 default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: if @DebugSwitch > 0 begin print dbo.QBM_FCVDatetimeToString(GETUTCDATE()) + ' ferdsch ' 17end return end 18