dbo.QBM_PDBQCS_SlotFill_Range
Stored ProcedureSQL_STORED_PROCEDURESandbox DB
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_FGIDBQueueSlotResetType source text reference
- references source dbo.QBM_PDBQCS_CurrentMoveSlot source text reference
- references source dbo.QBM_PDBQCS_SlotFill source text reference
- references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
- references source dbo.QBM_PJournal source text reference
- references source dbo.QBM_PSessionErrorClean source text reference
- references source dbo.QBM_PTransactionLevelCheck source text reference
References
- dbo.QBM_FGIDBQueueSlotResetType
- dbo.QBM_PDBQCS_CurrentMoveSlot
- dbo.QBM_PDBQCS_SlotFill
- dbo.QBM_PExecuteSQLWithRetry_LLP
- dbo.QBM_PJournal
- dbo.QBM_PSessionErrorClean
- dbo.QBM_PTransactionLevelCheck
Referenced By
- No direct source references extracted.
Complete Source
1CREATE PROCEDURE QBM_PDBQCS_SlotFill_Range(2 @SlotNumber int,3 @UID_TaskToStart varchar(38),4 @MaxBulk int,5 @LowerBound varchar(64),6 @UpperBound varchar(64),7 @CountParameter int,8 @RowsReadFromDBQueue int OUTPUT,9 @RowsFilledIntoCurrent int OUTPUT,10 @WasNoSuccess BIT = 0 OUTPUT11)12AS13BEGIN14 DECLARE @ErrorInLoadCurrent BIT = 015 DECLARE @DebugLevel char(1) = 'I'16 DECLARE @DebugSwitch int = 017 DECLARE @Message nvarchar(1000)18 DECLARE @SlotNumberSource int19 DECLARE @SlotNumberTarget int20 DECLARE @DBQueueToMove QBM_YDBQCSCurrentToMove21 DECLARE @RowsMoved int22 DECLARE @SQLMoveCmd nvarchar(max)23 DECLARE @FehlerImExec BIT = 024 SET XACT_ABORT OFF25 IF @DebugSwitch > 026 BEGIN27 DECLARE @TracePathlength int28 SELECT TOP 1 @TracePathlength = m.PathLength29 FROM QBMDBQueueTaskMetric m30 WHERE31 m.UID_Task = @UID_TaskToStart32 DECLARE @TraceMessage nvarchar(max)33 SELECT34 @TraceMessage = CONCAT('QBM_PDBQCS_SlotFill_Range ',35 convert(char(36), @UID_TaskToStart),36 ' Pathlength ',37 str(@TracePathlength),38 ' Slot ',39 str(@SlotNumber),40 ' Search ',41 @LowerBound,42 ' ',43 @UpperBound)44 EXEC QBM_PJournal @TraceMessage,45 @@procid,46 'T',47 @DebugLevel48 END49 BEGIN TRY50 EXEC QBM_PTransactionLevelCheck 0,51 @@procid,52 'Eintritt muß transaktionsfrei sein' drop TABLE53 IF EXISTS #UIDUmsetz54 CREATE TABLE #UIDUmsetz(55 UID_DialogDBQueue varchar(38) collate database_default NOT NULL,56 UID_Parameter varchar(38) collate database_default NULL,57 UID_SubParameter varchar(38) collate database_default NULL58 )59 CREATE index #QBM_YHelperQueueSlotFill260 ON #UIDUmsetz(61 UID_DialogDBQueue62 )63 IF @CountParameter = 064 BEGIN65 drop index66 IF EXISTS #UIDUmsetz.#QBM_YHelperQueueSlotFill267 INSERT INTO #UIDUmsetz(UID_DialogDBQueue68 )69 SELECT TOP(@MaxBulk70 ) q.UID_DialogDBQueue71 FROM DialogDBQueue q72 WITH(readpast,73 index(QBM_XC5DialogDBQueue),74 ForceSeek75)76WHERE77 q.UID_Task = @UID_TaskToStart AND q.Generation >= 078SELECT @RowsReadFromDBQueue = @@ROWCOUNT79CREATE index #QBM_YHelperQueueSlotFill280 ON #UIDUmsetz(81 UID_DialogDBQueue82)83IF @RowsReadFromDBQueue > 084BEGIN85 INSERT INTO #UIDUmsetz(UID_DialogDBQueue86)87SELECT q.UID_DialogDBQueue88FROM DialogDBQueue q89 WITH(readpast,90index(QBM_XC5DialogDBQueue),91ForceSeek92)93WHERE94 UID_Task = @UID_TaskToStart AND q.Generation >= 0 AND NOT EXISTS(95SELECT TOP 1 196FROM #UIDUmsetz qe97 WITH(index(#QBM_YHelperQueueSlotFill2),98ForceSeek)99WHERE100 qe.UID_DialogDBQueue = q.UID_DialogDBQueue101)102SELECT @RowsReadFromDBQueue += @@ROWCOUNT103END104END105IF @CountParameter = 1106BEGIN107 drop index108 IF EXISTS #UIDUmsetz.#QBM_YHelperQueueSlotFill2109 INSERT INTO #UIDUmsetz(UID_DialogDBQueue,110 UID_Parameter111)112SELECT TOP(@MaxBulk113) q.UID_DialogDBQueue,114q.Object115FROM DialogDBQueue q116 WITH(readpast,117index(QBM_XC8DialogDBQueue),118forceseek119)120WHERE121 q.UID_Task = @UID_TaskToStart AND q.Object >= @LowerBound AND q.Object < @UpperBound AND q.Generation >= 0122SELECT @RowsReadFromDBQueue = @@ROWCOUNT123CREATE index #QBM_YHelperQueueSlotFill2124 ON #UIDUmsetz(125 UID_DialogDBQueue126)127IF @RowsReadFromDBQueue > 0128BEGIN129 INSERT INTO #UIDUmsetz(UID_DialogDBQueue,130 UID_Parameter131)132SELECT133 DISTINCT q.UID_DialogDBQueue,134 q.object135FROM DialogDBQueue q136 WITH(readpast,137index(QBM_XC8DialogDBQueue),138ForceSeek139)140JOIN #UIDUmsetz p141 WITH(forceScan142)143 ON p.UID_Parameter = q.object144WHERE145 q.UID_Task = @UID_TaskToStart AND q.Generation >= 0 AND NOT EXISTS(146SELECT TOP 1 1147FROM #UIDUmsetz qe148 WITH(index(#QBM_YHelperQueueSlotFill2),149ForceSeek)150WHERE151 qe.UID_DialogDBQueue = q.UID_DialogDBQueue152)153SELECT @RowsReadFromDBQueue += @@ROWCOUNT154END155END156IF @CountParameter = 2157BEGIN158 drop index159 IF EXISTS #UIDUmsetz.#QBM_YHelperQueueSlotFill2160 INSERT INTO #UIDUmsetz(UID_DialogDBQueue,161 UID_Parameter,162 UID_SubParameter163)164SELECT TOP(@MaxBulk165) q.UID_DialogDBQueue,166q.Object,167q.SubObject168FROM DialogDBQueue q169 WITH(readpast,170index(QBM_XC8DialogDBQueue),171ForceSeek172)173WHERE174 q.UID_Task = @UID_TaskToStart AND q.Object >= @LowerBound AND q.Object < @UpperBound AND q.Generation >= 0175SELECT @RowsReadFromDBQueue = @@ROWCOUNT176CREATE index #QBM_YHelperQueueSlotFill2177 ON #UIDUmsetz(178 UID_DialogDBQueue179)180IF @RowsReadFromDBQueue > 0181BEGIN182 INSERT INTO #UIDUmsetz(UID_DialogDBQueue,183 UID_Parameter,184 UID_SubParameter185)186SELECT187 DISTINCT q.UID_DialogDBQueue,188 q.object,189 q.subobject190FROM DialogDBQueue q191 WITH(readpast,192index(QBM_XC8DialogDBQueue),193ForceSeek194)195JOIN #UIDUmsetz p196 WITH(ForceScan197)198 ON p.UID_Parameter = q.object AND p.UID_SubParameter = q.Subobject199WHERE200 q.UID_Task = @UID_TaskToStart AND q.Generation >= 0 AND NOT EXISTS(201SELECT TOP 1 1202FROM #UIDUmsetz qe203 WITH(index(#QBM_YHelperQueueSlotFill2),204ForceSeek)205WHERE206 qe.UID_DialogDBQueue = q.UID_DialogDBQueue207)208SELECT @RowsReadFromDBQueue += @@ROWCOUNT209END210END211IF EXISTS(212 SELECT TOP 1 1213 FROM QBMDBQueueCurrent214 WITH(readpast)215WHERE216 SlotNumber = @SlotNumber217)218BEGIN219 SELECT @SlotNumberSource = @SlotNumber220 SELECT @SlotnumberTarget = dbo.QBM_FGIDBQueueSlotResetType('Garbage01'221)222DELETE @DBQueueToMove223INSERT INTO @DBQueueToMove(UID_DialogDBQueue224)225SELECT cul.UID_DialogDBQueue226FROM QBMDBQueueCurrent cul227 WITH(readpast228)229WHERE230 cul.SlotNumber = @SlotNumber231EXEC @RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove,232 @SlotNumberSource,233 @SlotnumberTarget234IF @DebugSwitch > 0235BEGIN236 SELECT @Message = 'Slot ausgeputzt '237 EXEC QBM_PJournal @message,238 @@procid,239 'D',240 @Debuglevel241END242END243IF @DebugSwitch > 0244BEGIN245 SELECT246 @Message = CONCAT('@RowsReadFromDBQueue ',247 trim(str(@RowsReadFromDBQueue)),248 ' sätze ',249 ' Slot #',250 trim(str(@SlotNumber))251 )252EXEC QBM_PJournal @message,253 @@procid,254'T',255 @DebugLevel256END257IF @RowsReadFromDBQueue = 0258BEGIN259 GOTO endlabel260END261SELECT @RowsFilledIntoCurrent = @RowsReadFromDBQueue262SELECT263 @SQLMoveCmd = CONCAT('declare @JustNow datetime = getutcdate()264 ',265 'insert into QBMDBQueueCurrent 266 (UID_DialogDBQueue, UID_Parameter, UID_SubParameter, SlotNumber, UID_Task, GenProcID, Generation, StartedAt )267 select q.UID_DialogDBQueue, q.Object, q.SubObject, '268 ,269 str(@SlotNumber),270 ', q.UID_Task, q.GenProcID , q.Generation, @JustNow271 from DialogDBQueue q with (readpast, ForceSeek) join #UIDUmsetz u with (ForceScan) on q.UID_DialogDBQueue = u.UID_DialogDBQueue272 '273 ,274 '; ',275 'update DialogDBQueue 276 set Generation = -1277 FROM DialogDBQueue q with (ForceSeek) join #UIDUmsetz u with (ForceScan) on q.UID_DialogDBQueue = u.UID_DialogDBQueue278 '279 )280BEGIN TRY281 EXEC @RowsFilledIntoCurrent = QBM_PExecuteSQLWithRetry_LLP @SQLMoveCmd,282 @LockTimeout_ms = 500,283 @MaxWaitTimeForLock_s = 8.0,284 @ProcIDForJournal = NULL,285 @HandleErrorSilent = 1,286 @DeadlockPriority = 0,287 @ExecuteWithTransact = 1,288 @ErrorInExecute = @FehlerImExec OUTPUT289 SELECT @WasNoSuccess = @FehlerImExec290END TRY291BEGIN CATCH292 SELECT @WasNoSuccess = 1293 EXEC QBM_PSessionErrorClean294END CATCH295END TRY296BEGIN CATCH297 SELECT @WasNoSuccess = 1298END CATCH299BEGIN TRY300 truncate TABLE #UIDUmsetz301END TRY302BEGIN CATCH303END CATCH304BEGIN TRY305 IF @@TRANCOUNT > 0306 BEGIN307 ROLLBACK TRANSACTION308 SELECT @WasNoSuccess = 1309 END310END TRY311BEGIN CATCH312 SELECT @WasNoSuccess = 1313END CATCH314endLabel:315IF @WasNoSuccess = 1316BEGIN317 EXEC QBM_PJournal '@WasNoSuccess = 1',318 @@procid,319 'T',320 @DebugLevel321END322RETURN323ENDOpen raw exported source
1 create procedure QBM_PDBQCS_SlotFill_Range ( @SlotNumber int , @UID_TaskToStart varchar(38) , @MaxBulk int , @LowerBound varchar(64) , @UpperBound2 varchar(64) , @CountParameter int , @RowsReadFromDBQueue int output , @RowsFilledIntoCurrent int output , @WasNoSuccess bit = 0 output ) as3 begin declare @ErrorInLoadCurrent bit = 0 declare @DebugLevel char(1) = 'I' declare @DebugSwitch int = 0 declare @Message nvarchar(1000) declare @SlotNumberSource4 int declare @SlotNumberTarget int declare @DBQueueToMove QBM_YDBQCSCurrentToMove declare @RowsMoved int declare @SQLMoveCmd nvarchar(max) declare @FehlerImExec5 bit = 0 SET XACT_ABORT OFF if @DebugSwitch > 0 begin declare @TracePathlength int select top 1 @TracePathlength = m.PathLength from QBMDBQueueTaskMetric6 m where m.UID_Task = @UID_TaskToStart declare @TraceMessage nvarchar(max) select @TraceMessage = concat('QBM_PDBQCS_SlotFill_Range ', convert(char(36)7, @UID_TaskToStart) , ' Pathlength ', str(@TracePathlength) , ' Slot ' , str(@SlotNumber) , ' Search ' , @LowerBound, ' ', @UpperBound ) exec QBM_PJournal8 @TraceMessage, @@procid, 'T', @DebugLevel end BEGIN TRY exec QBM_PTransactionLevelCheck 0, @@procid, 'Eintritt muß transaktionsfrei sein' drop table if9 exists #UIDUmsetz create table #UIDUmsetz ( UID_DialogDBQueue varchar(38) collate database_default NOT NULL , UID_Parameter varchar(38) collate database_default10 NULL , UID_SubParameter varchar(38) collate database_default NULL ) create index #QBM_YHelperQueueSlotFill2 on #UIDUmsetz (UID_DialogDBQueue) if @CountParameter11 = 0 begin drop index if exists #UIDUmsetz.#QBM_YHelperQueueSlotFill2 insert into #UIDUmsetz(UID_DialogDBQueue) select top (@MaxBulk) q.UID_DialogDBQueue12 from DialogDBQueue q with (readpast, index (QBM_XC5DialogDBQueue), ForceSeek) where q.UID_Task = @UID_TaskToStart and q.Generation >= 0 select @RowsReadFromDBQueue13 = @@ROWCOUNT create index #QBM_YHelperQueueSlotFill2 on #UIDUmsetz (UID_DialogDBQueue) if @RowsReadFromDBQueue > 0 begin insert into #UIDUmsetz(UID_DialogDBQueue14) select q.UID_DialogDBQueue from DialogDBQueue q with (readpast, index (QBM_XC5DialogDBQueue), ForceSeek) where UID_Task = @UID_TaskToStart and q.Generation15 >= 0 and not exists (select top 1 1 from #UIDUmsetz qe with (index(#QBM_YHelperQueueSlotFill2), ForceSeek) where qe.UID_DialogDBQueue = q.UID_DialogDBQueue16 ) select @RowsReadFromDBQueue += @@ROWCOUNT end end if @CountParameter = 1 begin drop index if exists #UIDUmsetz.#QBM_YHelperQueueSlotFill2 insert 17into #UIDUmsetz(UID_DialogDBQueue, UID_Parameter) select top (@MaxBulk) q.UID_DialogDBQueue, q.Object from DialogDBQueue q with (readpast, index (QBM_XC8DialogDBQueue18), forceseek) where q.UID_Task = @UID_TaskToStart and q.Object >= @LowerBound and q.Object < @UpperBound and q.Generation >= 0 select @RowsReadFromDBQueue19 = @@ROWCOUNT create index #QBM_YHelperQueueSlotFill2 on #UIDUmsetz (UID_DialogDBQueue) if @RowsReadFromDBQueue > 0 begin insert into #UIDUmsetz (UID_DialogDBQueue20, UID_Parameter) select distinct q.UID_DialogDBQueue, q.object from DialogDBQueue q with (readpast, index (QBM_XC8DialogDBQueue), ForceSeek) join #UIDUmsetz21 p with (forceScan) on p.UID_Parameter = q.object where q.UID_Task = @UID_TaskToStart and q.Generation >= 0 and not exists (select top 1 1 from #UIDUmsetz22 qe with (index(#QBM_YHelperQueueSlotFill2), ForceSeek) where qe.UID_DialogDBQueue = q.UID_DialogDBQueue ) select @RowsReadFromDBQueue += @@ROWCOUNT end23 end if @CountParameter = 2 begin drop index if exists #UIDUmsetz.#QBM_YHelperQueueSlotFill2 insert into #UIDUmsetz(UID_DialogDBQueue, UID_Parameter24, UID_SubParameter) select top (@MaxBulk) q.UID_DialogDBQueue, q.Object, q.SubObject from DialogDBQueue q with (readpast, index (QBM_XC8DialogDBQueue),25 ForceSeek) where q.UID_Task = @UID_TaskToStart and q.Object >= @LowerBound and q.Object < @UpperBound and q.Generation >= 0 select @RowsReadFromDBQueue26 = @@ROWCOUNT create index #QBM_YHelperQueueSlotFill2 on #UIDUmsetz (UID_DialogDBQueue) if @RowsReadFromDBQueue > 0 begin insert into #UIDUmsetz(UID_DialogDBQueue27, UID_Parameter, UID_SubParameter) select distinct q.UID_DialogDBQueue, q.object, q.subobject from DialogDBQueue q with (readpast , index (QBM_XC8DialogDBQueue28), ForceSeek) join #UIDUmsetz p with (ForceScan) on p.UID_Parameter = q.object and p.UID_SubParameter = q.Subobject where q.UID_Task = @UID_TaskToStart29 and q.Generation >= 0 and not exists (select top 1 1 from #UIDUmsetz qe with (index(#QBM_YHelperQueueSlotFill2), ForceSeek) where qe.UID_DialogDBQueue30 = q.UID_DialogDBQueue ) select @RowsReadFromDBQueue += @@ROWCOUNT end end if exists (select top 1 1 from QBMDBQueueCurrent with (readpast) where SlotNumber31 = @SlotNumber ) begin select @SlotNumberSource = @SlotNumber select @SlotnumberTarget = dbo.QBM_FGIDBQueueSlotResetType('Garbage01') delete @DBQueueToMove32 insert into @DBQueueToMove(UID_DialogDBQueue) select cul.UID_DialogDBQueue from QBMDBQueueCurrent cul with (readpast) where cul.SlotNumber = @SlotNumber33 exec @RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove, @SlotNumberSource, @SlotnumberTarget if @DebugSwitch > 0 begin select @Message = 'Slot ausgeputzt '34 exec QBM_PJournal @message , @@procid, 'D', @Debuglevel end end if @DebugSwitch > 0 begin select @Message = concat('@RowsReadFromDBQueue ', trim(str(@RowsReadFromDBQueue35)) , ' sätze ' , ' Slot #', trim(str(@SlotNumber))) exec QBM_PJournal @message , @@procid, 'T', @DebugLevel end if @RowsReadFromDBQueue = 0 begin goto 36endlabel end select @RowsFilledIntoCurrent = @RowsReadFromDBQueue select @SQLMoveCmd = concat( 'declare @JustNow datetime = getutcdate()37 ',38 'insert into QBMDBQueueCurrent 39 (UID_DialogDBQueue, UID_Parameter, UID_SubParameter, SlotNumber, UID_Task, GenProcID, Generation, StartedAt )40 select q.UID_DialogDBQueue, q.Object, q.SubObject, '41, str(@SlotNumber) , ', q.UID_Task, q.GenProcID , q.Generation, @JustNow42 from DialogDBQueue q with (readpast, ForceSeek) join #UIDUmsetz u with (ForceScan) on q.UID_DialogDBQueue = u.UID_DialogDBQueue43 '44 , '; ', 'update DialogDBQueue 45 set Generation = -146 FROM DialogDBQueue q with (ForceSeek) join #UIDUmsetz u with (ForceScan) on q.UID_DialogDBQueue = u.UID_DialogDBQueue47 '48 ) BEGIN TRY exec @RowsFilledIntoCurrent = QBM_PExecuteSQLWithRetry_LLP @SQLMoveCmd , @LockTimeout_ms = 500 , @MaxWaitTimeForLock_s = 8.0 , @ProcIDForJournal49 = null , @HandleErrorSilent = 1 , @DeadlockPriority = 0 , @ExecuteWithTransact = 1 , @ErrorInExecute = @FehlerImExec output select @WasNoSuccess 50= @FehlerImExec END TRY BEGIN CATCH select @WasNoSuccess = 1 exec QBM_PSessionErrorClean END CATCH END TRY BEGIN CATCH select @WasNoSuccess = 1 END CATCH51 BEGIN TRY truncate table #UIDUmsetz END TRY BEGIN CATCH END CATCH BEGIN TRY if @@TRANCOUNT > 0 begin rollback transaction select @WasNoSuccess = 1 end52 END TRY BEGIN CATCH select @WasNoSuccess = 1 END CATCH endLabel: if @WasNoSuccess = 1 begin exec QBM_PJournal '@WasNoSuccess = 1', @@procid, 'T', @DebugLevel53 end return end 54