dbo.QBM_PQBMDBQueueCurrentDelete

1  create   procedure QBM_PQBMDBQueueCurrentDelete ( @RowLimit int = 1000  , @MaxLimit int = 50000  ) as begin declare @Items QBM_YSingleGuid  declare
2 @ItemPortion QBM_YSingleGuid declare @einRuck int  declare @LaufDelete int = 0 declare @AnzahlLoesch int declare @AnzahlLoeschGesamt int = 0 declare @Start
3 datetime declare @ende datetime declare @diff int declare @ExpectedRuntime float = 1000.0 declare @anzahl int SET XACT_ABORT OFF BEGIN TRY set lock_timeout
4 50 select @anzahl = dbo.QBM_FGITableCountAll('QBMDBQueueCurrent') if @anzahl > 0 begin goto normal end if @anzahl = 0 and @@TRANCOUNT = 0  begin begin
5 transaction BEGIN TRY  update QBMDBQueueCurrent set StartedAt = q.StartedAt from QBMDBQueueCurrent q with (tablockx) END TRY BEGIN CATCH select @anzahl
6 = 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 into
8 @Items (UID_SingleGuid ) select top (@MaxLimit) qt.UID_DialogDBQueue from QBMDBQueueCurrent qt with (readpast) where qt.SlotNumber = 0  select @LaufDelete
9 = 1 select @einRuck = @RowLimit while @LaufDelete > 0 begin select @Start = getutcdate() delete  @ItemPortion  insert into @ItemPortion (UID_SingleGuid
10 ) 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 QBMDBQueueCurrent
12 cu with (forceSeek) where cu.UID_DialogDBQueue in (select t.UID_SingleGuid  from @ItemPortion t ) select @AnzahlLoesch = @@rowcount END TRY BEGIN CATCH
13  select @AnzahlLoesch = 0 exec QBM_PWaitForSeconds 0.05 end CATCH select @AnzahlLoeschGesamt += @AnzahlLoesch select @ende = getutcdate() select @diff
14 = DATEDIFF(ms, @start, @ende) select @einRuck = case when @diff = 0 then @RowLimit when @AnzahlLoesch = 0 then @RowLimit when @diff > @ExpectedRuntime
15 then @RowLimit else convert(int, @ExpectedRuntime * convert(float, @einRuck) / convert(float, @diff)) end  end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd
16 default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return (@AnzahlLoeschGesamt
17) end 
18