Back to OIM Explorer

dbo.QBM_PDialogDBQueueDelete

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.465 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_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_FGITableCountAll source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PWaitForSeconds source text reference

References

Referenced By

Complete Source

SQL128 lines
1CREATE PROCEDURE QBM_PDialogDBQueueDelete(2  @RowLimit int = 1000,3  @MaxLimit int = 500004)5AS6BEGIN7  DECLARE @Items QBM_YSingleGuid8  DECLARE @ItemPortion QBM_YSingleGuid9  DECLARE @einRuck int10  DECLARE @LaufDelete int = 011  DECLARE @AnzahlLoesch int12  DECLARE @AnzahlLoeschGesamt int = 013  DECLARE @Start datetime14  DECLARE @ende datetime15  DECLARE @diff int16  DECLARE @ExpectedRuntime float = 1000.017  DECLARE @anzahl int18  SET XACT_ABORT OFF19  BEGIN TRY20    SET lock_timeout 5021    SELECT @anzahl = dbo.QBM_FGITableCountAll('DialogDBQueue')22    IF @anzahl > 023    BEGIN24      GOTO normal25    END26    IF @anzahl = 0 AND @@TRANCOUNT = 027    BEGIN28      BEGIN29        TRANSACTION30        BEGIN TRY31          UPDATE DialogDBQueue32          SET Generation = q.Generation33          FROM DialogDBQueue q34            WITH(tablockx)35        END TRY36        BEGIN CATCH37          SELECT @anzahl = 138        END CATCH39        IF dbo.QBM_FGITableCountAll('DialogDBQueue') = 0 AND @anzahl = 040        BEGIN41          BEGIN TRY42            truncate TABLE DialogDBQueue COMMIT43            SELECT @LaufDelete = 144          END TRY45          BEGIN CATCH46            ROLLBACK47          END CATCH48        END49        ELSE50        BEGIN51          ROLLBACK52        END53      END54      IF @LaufDelete = 155      BEGIN56        GOTO endLabel57      END58      normal:59      INSERT INTO @Items(UID_SingleGuid)60      SELECT TOP(@MaxLimit) qt.UID_DialogDBQueue61      FROM DialogDBQueue qt62        WITH(readpast)63      WHERE64        Generation = -165      SELECT @LaufDelete = 166      SELECT @einRuck = @RowLimit67      WHILE @LaufDelete > 068      BEGIN69        SELECT @Start = getutcdate()70        DELETE @ItemPortion71        INSERT INTO @ItemPortion(UID_SingleGuid)72        SELECT TOP(@einRuck) t.UID_SingleGuid73        FROM @Items t74        WHERE75          t.BitProperty = 076        SELECT @LaufDelete = @@ROWCOUNT77        IF @LaufDelete = 078        BEGIN79          CONTINUE80        END81        UPDATE @Items82        SET BitProperty = 183        FROM @Items t84        JOIN @ItemPortion p85          ON t.UID_SingleGuid = p.UID_SingleGuid86        BEGIN TRY87          DELETE DialogDBQueue88          FROM DialogDBQueue q89            WITH(forceSeek)90          WHERE91            q.UID_DialogDBQueue IN(92          SELECT t.UID_SingleGuid93          FROM @ItemPortion t)94          SELECT @AnzahlLoesch = @@rowcount95        END TRY96        BEGIN CATCH97          SELECT @AnzahlLoesch = 098          EXEC QBM_PWaitForSeconds 0.0599        END CATCH100        SELECT @AnzahlLoeschGesamt += @AnzahlLoesch101        SELECT @ende = getutcdate()102        SELECT103          @diff = DATEDIFF(ms,104          @start,105          @ende)106        SELECT @einRuck = CASE107        WHEN @diff = 0 THEN108        @RowLimit109        WHEN @AnzahlLoesch = 0 THEN110        @RowLimit111        WHEN @diff > @ExpectedRuntime THEN112        @RowLimit113        ELSE convert(int,114        @ExpectedRuntime * convert(float, @einRuck) / convert(float, @diff))115        END116      END117    END TRY118    BEGIN CATCH119      EXEC QBM_PSessionErrorAdd DEFAULT120      DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()121      RAISERROR(@Rethrow,122      18,123      1)124        WITH NOWAIT125    END CATCH126    endLabel:127    RETURN(@AnzahlLoeschGesamt)128  END
Open raw exported source
SQL ยท Raw17 lines
1  create   procedure QBM_PDialogDBQueueDelete ( @RowLimit int = 1000  , @MaxLimit int = 50000  ) as begin declare @Items QBM_YSingleGuid  declare2 @ItemPortion QBM_YSingleGuid declare @einRuck int  declare @LaufDelete int = 0 declare @AnzahlLoesch int declare @AnzahlLoeschGesamt int = 0 declare @Start3 datetime declare @ende datetime declare @diff int declare @ExpectedRuntime float = 1000.0 declare @anzahl int SET XACT_ABORT OFF BEGIN TRY set lock_timeout4 50 select @anzahl = dbo.QBM_FGITableCountAll('DialogDBQueue') if @anzahl > 0 begin goto normal end if @anzahl = 0 and @@TRANCOUNT = 0  begin begin transaction5 BEGIN TRY  update DialogDBQueue set Generation = q.Generation from DialogDBQueue q with (tablockx) END TRY BEGIN CATCH select @anzahl = 1  END CATCH  6if dbo.QBM_FGITableCountAll('DialogDBQueue') = 0 and @anzahl = 0 begin BEGIN TRY  truncate table DialogDBQueue  commit select @LaufDelete = 1 END TRY BEGIN7 CATCH   rollback END CATCH end else begin  rollback end end  if @LaufDelete = 1 begin goto endLabel end normal: insert into @Items (UID_SingleGuid ) select8 top (@MaxLimit) qt.UID_DialogDBQueue from DialogDBQueue qt with (readpast) where Generation = -1 select @LaufDelete = 1 select @einRuck = @RowLimit while9 @LaufDelete > 0 begin select @Start = getutcdate() delete  @ItemPortion  insert into @ItemPortion (UID_SingleGuid ) select top (@einRuck) t.UID_SingleGuid10 from @Items t where t.BitProperty = 0  select @LaufDelete = @@ROWCOUNT if @LaufDelete = 0 begin continue end update @Items set BitProperty = 1  from @Items11 t join @ItemPortion p on t.UID_SingleGuid = p.UID_SingleGuid  BEGIN TRY delete DialogDBQueue from DialogDBQueue q with (forceSeek) where q.UID_DialogDBQueue12 in (select t.UID_SingleGuid  from @ItemPortion t ) select @AnzahlLoesch = @@rowcount END TRY BEGIN CATCH  select @AnzahlLoesch = 0 exec QBM_PWaitForSeconds13 0.05 end CATCH select @AnzahlLoeschGesamt += @AnzahlLoesch select @ende = getutcdate() select @diff = DATEDIFF(ms, @start, @ende) select @einRuck = case14 when @diff = 0 then @RowLimit when @AnzahlLoesch = 0 then @RowLimit when @diff > @ExpectedRuntime then @RowLimit else convert(int, @ExpectedRuntime * 15convert(float, @einRuck) / convert(float, @diff)) end  end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow16() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return (@AnzahlLoeschGesamt) end 17