Back to OIM Explorer

dbo.QBM_PQBMDBQueueCurrentDelete

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

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

dbo.QBM_PWorkDBQueueDelete

Complete Source

1CREATE PROCEDURE QBM_PQBMDBQueueCurrentDelete(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('QBMDBQueueCurrent')22    IF @anzahl > 023    BEGIN24      GOTO normal25    END26    IF @anzahl = 0 AND @@TRANCOUNT = 027    BEGIN28      BEGIN29        TRANSACTION30        BEGIN TRY31          UPDATE QBMDBQueueCurrent32          SET StartedAt = q.StartedAt33          FROM QBMDBQueueCurrent q34            WITH(tablockx)35        END TRY36        BEGIN CATCH37          SELECT @anzahl = 138        END CATCH39        IF dbo.QBM_FGITableCountAll('QBMDBQueueCurrent') = 0 AND @anzahl = 040        BEGIN41          BEGIN TRY42            truncate TABLE QBMDBQueueCurrent 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 QBMDBQueueCurrent qt62        WITH(readpast)63      WHERE64        qt.SlotNumber = 065      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 QBMDBQueueCurrent88          FROM QBMDBQueueCurrent cu89            WITH(forceSeek)90          WHERE91            cu.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

1  create   procedure QBM_PQBMDBQueueCurrentDelete ( @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('QBMDBQueueCurrent') if @anzahl > 0 begin goto normal end if @anzahl = 0 and @@TRANCOUNT = 0  begin begin5 transaction BEGIN TRY  update QBMDBQueueCurrent set StartedAt = q.StartedAt from QBMDBQueueCurrent q with (tablockx) END TRY BEGIN CATCH select @anzahl6 = 1  END CATCH  if dbo.QBM_FGITableCountAll('QBMDBQueueCurrent') = 0 and @anzahl = 0 begin BEGIN TRY  truncate table QBMDBQueueCurrent  commit select 7@LaufDelete = 1 END TRY BEGIN CATCH   rollback END CATCH end else begin  rollback end end if @LaufDelete = 1 begin goto endLabel end normal: insert into8 @Items (UID_SingleGuid ) select top (@MaxLimit) qt.UID_DialogDBQueue from QBMDBQueueCurrent qt with (readpast) where qt.SlotNumber = 0  select @LaufDelete9 = 1 select @einRuck = @RowLimit while @LaufDelete > 0 begin select @Start = getutcdate() delete  @ItemPortion  insert into @ItemPortion (UID_SingleGuid10 ) select top (@einRuck) t.UID_SingleGuid from @Items t where t.BitProperty = 0  select @LaufDelete = @@ROWCOUNT if @LaufDelete = 0 begin continue end 11update @Items set BitProperty = 1  from @Items t join @ItemPortion p on t.UID_SingleGuid = p.UID_SingleGuid  BEGIN TRY delete QBMDBQueueCurrent from QBMDBQueueCurrent12 cu with (forceSeek) where cu.UID_DialogDBQueue in (select t.UID_SingleGuid  from @ItemPortion t ) select @AnzahlLoesch = @@rowcount END TRY BEGIN CATCH13  select @AnzahlLoesch = 0 exec QBM_PWaitForSeconds 0.05 end CATCH select @AnzahlLoeschGesamt += @AnzahlLoesch select @ende = getutcdate() select @diff14 = DATEDIFF(ms, @start, @ende) select @einRuck = case when @diff = 0 then @RowLimit when @AnzahlLoesch = 0 then @RowLimit when @diff > @ExpectedRuntime15 then @RowLimit else convert(int, @ExpectedRuntime * convert(float, @einRuck) / convert(float, @diff)) end  end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd16 default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return (@AnzahlLoeschGesamt17) end 18