Back to OIM Explorer

dbo.QBM_PDBQCS_SlotFill

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.813 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_FGIDBQueueSlotResetType source text reference
  • references source dbo.QBM_PDBQCS_CurrentMoveSlot 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

Referenced By

Complete Source

SQL293 lines
1CREATE PROCEDURE QBM_PDBQCS_SlotFill(2  @SlotNumber int,3  @UID_TaskToStart varchar(38),4  @MaxBulk int,5  @CountParameter int,6  @SearchPattern varchar(64),7  @RowsReadFromDBQueue int OUTPUT,8  @RowsFilledIntoCurrent int OUTPUT,9  @WasNoSuccess BIT = 0 OUTPUT10)11AS12BEGIN13  DECLARE @ErrorInLoadCurrent BIT = 014  DECLARE @DebugLevel char(1) = 'I'15  DECLARE @DebugSwitch int = 016  DECLARE @Message nvarchar(1000)17  DECLARE @SlotNumberSource int18  DECLARE @SlotNumberTarget int19  DECLARE @DBQueueToMove QBM_YDBQCSCurrentToMove20  DECLARE @RowsMoved int21  DECLARE @SQLMoveCmd nvarchar(max)22  DECLARE @FehlerImExec BIT = 023  SET XACT_ABORT OFF24  IF @DebugSwitch > 025  BEGIN26    DECLARE @TracePathlength int27    SELECT TOP 1 @TracePathlength = m.PathLength28    FROM QBMDBQueueTaskMetric m29    WHERE30      m.UID_Task = @UID_TaskToStart31    DECLARE @TraceMessage nvarchar(max)32    SELECT33      @TraceMessage = CONCAT('QBM_PDBQCS_SlotFill ',34      convert(char(36), @UID_TaskToStart),35      ' Pathlength ',36      str(@TracePathlength),37      ' Slot ',38      str(@SlotNumber),39      ' Search ',40      @SearchPattern)41    EXEC QBM_PJournal @TraceMessage,42      @@procid,43    'T',44      @DebugLevel45  END46  BEGIN TRY47    EXEC QBM_PTransactionLevelCheck 0,48      @@procid,49    'Eintritt muß transaktionsfrei sein' drop TABLE50    IF EXISTS #UIDUmsetz51    CREATE TABLE #UIDUmsetz(52      UID_DialogDBQueue varchar(38) collate database_default NOT NULL,53      UID_Parameter varchar(38) collate database_default NULL,54      UID_SubParameter varchar(38) collate database_default NULL,55      SlotNumber int DEFAULT 0,56      index QBM_YHelperQueueSlotFill1(UID_Parameter,57      UID_SubParameter),58      index QBM_YHelperQueueSlotFill2(UID_DialogDBQueue)59    )60    IF @CountParameter = 061    BEGIN62      INSERT INTO #UIDUmsetz(UID_DialogDBQueue,63      SlotNumber64    )65    SELECT TOP(@MaxBulk66  ) q.UID_DialogDBQueue,67  @SlotNumber68  FROM DialogDBQueue q69    WITH(readpast70)71WHERE72  q.UID_Task = @UID_TaskToStart AND q.Generation >= 073SELECT @RowsReadFromDBQueue = @@ROWCOUNT74IF @RowsReadFromDBQueue > 075BEGIN76  INSERT INTO #UIDUmsetz(UID_DialogDBQueue,77  SlotNumber78)79SELECT80  q.UID_DialogDBQueue,81  @SlotNumber82FROM DialogDBQueue q83  WITH(readpast84)85WHERE86  UID_Task = @UID_TaskToStart AND q.Generation >= 0 AND NOT EXISTS(87SELECT TOP 1 188FROM #UIDUmsetz qe89WHERE90  qe.UID_DialogDBQueue = q.UID_DialogDBQueue91)92SELECT @RowsReadFromDBQueue += @@ROWCOUNT93END94END95IF @CountParameter = 196BEGIN97  INSERT INTO #UIDUmsetz(UID_DialogDBQueue,98  UID_Parameter,99  SlotNumber100)101SELECT TOP(@MaxBulk102) q.UID_DialogDBQueue,103q.Object,104@SlotNumber105FROM DialogDBQueue q106  WITH(readpast107)108WHERE109  q.UID_Task = @UID_TaskToStart AND q.Object LIKE @SearchPattern AND q.Generation >= 0110SELECT @RowsReadFromDBQueue = @@ROWCOUNT111IF @RowsReadFromDBQueue > 0112BEGIN113  INSERT INTO #UIDUmsetz(UID_DialogDBQueue,114  UID_Parameter,115  SlotNumber116)117SELECT118  DISTINCT q.UID_DialogDBQueue,119  q.object,120  @SlotNumber121FROM DialogDBQueue q122  WITH(readpast123)124JOIN #UIDUmsetz p125  ON p.UID_Parameter = q.object126WHERE127  q.UID_Task = @UID_TaskToStart AND q.Generation >= 0 AND NOT EXISTS(128SELECT TOP 1 1129FROM #UIDUmsetz qe130WHERE131  qe.UID_DialogDBQueue = q.UID_DialogDBQueue132)133SELECT @RowsReadFromDBQueue += @@ROWCOUNT134END135END136IF @CountParameter = 2137BEGIN138  INSERT INTO #UIDUmsetz(UID_DialogDBQueue,139  UID_Parameter,140  UID_SubParameter,141  SlotNumber142)143SELECT TOP(@MaxBulk144) q.UID_DialogDBQueue,145q.Object,146q.SubObject,147@SlotNumber148FROM DialogDBQueue q149  WITH(readpast150)151WHERE152  q.UID_Task = @UID_TaskToStart AND q.Object LIKE @SearchPattern AND q.Generation >= 0153SELECT @RowsReadFromDBQueue = @@ROWCOUNT154IF @RowsReadFromDBQueue > 0155BEGIN156  INSERT INTO #UIDUmsetz(UID_DialogDBQueue,157  UID_Parameter,158  UID_SubParameter,159  SlotNumber160)161SELECT162  DISTINCT q.UID_DialogDBQueue,163  q.object,164  q.subobject,165  @SlotNumber166FROM DialogDBQueue q167  WITH(readpast168)169JOIN #UIDUmsetz p170  ON p.UID_Parameter = q.object AND p.UID_SubParameter = q.Subobject171WHERE172  q.UID_Task = @UID_TaskToStart AND q.Generation >= 0 AND NOT EXISTS(173SELECT TOP 1 1174FROM #UIDUmsetz qe175WHERE176  qe.UID_DialogDBQueue = q.UID_DialogDBQueue177)178SELECT @RowsReadFromDBQueue += @@ROWCOUNT179END180END181IF EXISTS(182  SELECT TOP 1 1183  FROM QBMDBQueueCurrent184  WITH(readpast)185WHERE186  SlotNumber = @SlotNumber187)188BEGIN189  SELECT @SlotNumberSource = @SlotNumber190  SELECT @SlotnumberTarget = dbo.QBM_FGIDBQueueSlotResetType('Garbage01'191)192DELETE @DBQueueToMove193INSERT INTO @DBQueueToMove(UID_DialogDBQueue194)195SELECT cul.UID_DialogDBQueue196FROM QBMDBQueueCurrent cul197  WITH(readpast198)199WHERE200  cul.SlotNumber = @SlotNumber201EXEC @RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove,202  @SlotNumberSource,203  @SlotnumberTarget204IF @DebugSwitch > 0205BEGIN206  SELECT @Message = 'Slot ausgeputzt '207  EXEC QBM_PJournal @message,208    @@procid,209  'D',210    @Debuglevel211END212END213IF @DebugSwitch > 0214BEGIN215  SELECT216    @Message = CONCAT('@RowsReadFromDBQueue ',217    trim(str(@RowsReadFromDBQueue)),218    ' sätze ',219    ' Slot #',220    trim(str(@SlotNumber))221    )222EXEC QBM_PJournal @message,223  @@procid,224'T',225  @DebugLevel226END227IF @RowsReadFromDBQueue = 0228BEGIN229  GOTO endlabel230END231SELECT @RowsFilledIntoCurrent = @RowsReadFromDBQueue232SELECT233  @SQLMoveCmd = '234 				insert into QBMDBQueueCurrent 235					(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, SlotNumber, UID_Task, GenProcID, Generation, StartedAt  )236				select q.UID_DialogDBQueue, q.Object, q.SubObject, u.SlotNumber, q.UID_Task, q.GenProcID , q.Generation, getutcdate() 237					from DialogDBQueue q /*with (readpast) */ join #UIDUmsetz u on q.UID_DialogDBQueue = u.UID_DialogDBQueue238				-- noch ein Hosenträger, den wir erst mal weglassen:239				--										left outer join QBMDBQueueCurrent cu with (readpast) on cu.UID_DialogDBQueue = q.UID_DialogDBQueue240				--where cu.UID_DialogDBQueue is null241242				; update DialogDBQueue 243					set Generation = -1244				 FROM DialogDBQueue q join #UIDUmsetz u on q.UID_DialogDBQueue = u.UID_DialogDBQueue245				-- noch ein Hosenträger, den wir erst mal weglassen:246									--join QBMDBQueueCurrent cu with (readpast) on cu.UID_DialogDBQueue = q.UID_DialogDBQueue247					-- falls Wiederholversuche anfallen248				 --where q.Generation >= 0249				 '250BEGIN TRY251  EXEC @RowsFilledIntoCurrent = QBM_PExecuteSQLWithRetry_LLP @SQLMoveCmd,252    @LockTimeout_ms = 500,253    @MaxWaitTimeForLock_s = 8.0,254    @ProcIDForJournal = NULL,255    @HandleErrorSilent = 1,256    @DeadlockPriority = 0,257    @ExecuteWithTransact = 1,258    @ErrorInExecute = @FehlerImExec OUTPUT259  SELECT @WasNoSuccess = @FehlerImExec260END TRY261BEGIN CATCH262  SELECT @WasNoSuccess = 1263  EXEC QBM_PSessionErrorClean264END CATCH265END TRY266BEGIN CATCH267  SELECT @WasNoSuccess = 1268END CATCH269BEGIN TRY270  truncate TABLE #UIDUmsetz271END TRY272BEGIN CATCH273END CATCH274BEGIN TRY275  IF @@TRANCOUNT > 0276  BEGIN277    ROLLBACK TRANSACTION278    SELECT @WasNoSuccess = 1279  END280END TRY281BEGIN CATCH282  SELECT @WasNoSuccess = 1283END CATCH284endLabel:285IF @WasNoSuccess = 1286BEGIN287  EXEC QBM_PJournal '@WasNoSuccess = 1',288    @@procid,289  'T',290    @DebugLevel291END292RETURN293END
Open raw exported source
SQL · Raw55 lines
1   create   procedure QBM_PDBQCS_SlotFill ( @SlotNumber int , @UID_TaskToStart varchar(38) , @MaxBulk int   , @CountParameter int , @SearchPattern2 varchar(64)  , @RowsReadFromDBQueue int output    , @RowsFilledIntoCurrent int output     , @WasNoSuccess bit = 0 output   ) as begin declare @ErrorInLoadCurrent3 bit = 0 declare @DebugLevel char(1) = 'I' declare @DebugSwitch int = 0 declare @Message nvarchar(1000)    declare @SlotNumberSource int declare @SlotNumberTarget4 int declare @DBQueueToMove QBM_YDBQCSCurrentToMove declare @RowsMoved int declare @SQLMoveCmd nvarchar(max) declare @FehlerImExec bit = 0   SET XACT_ABORT5 OFF if @DebugSwitch > 0 begin declare @TracePathlength int select top 1 @TracePathlength = m.PathLength from QBMDBQueueTaskMetric m where m.UID_Task =6 @UID_TaskToStart declare @TraceMessage nvarchar(max) select @TraceMessage = concat('QBM_PDBQCS_SlotFill ', convert(char(36), @UID_TaskToStart) , ' Pathlength '7, str(@TracePathlength) , ' Slot ' , str(@SlotNumber) , ' Search ' , @SearchPattern ) exec QBM_PJournal @TraceMessage, @@procid, 'T', @DebugLevel end BEGIN8 TRY exec QBM_PTransactionLevelCheck 0, @@procid, 'Eintritt muß transaktionsfrei sein' drop table if exists #UIDUmsetz create table #UIDUmsetz ( UID_DialogDBQueue9 varchar(38) collate database_default NOT NULL , UID_Parameter varchar(38) collate database_default NULL , UID_SubParameter varchar(38) collate database_default10 NULL , SlotNumber int default 0  , index QBM_YHelperQueueSlotFill1 (UID_Parameter, UID_SubParameter) , index QBM_YHelperQueueSlotFill2 (UID_DialogDBQueue11) )  if @CountParameter = 0 begin insert into #UIDUmsetz(UID_DialogDBQueue, SlotNumber) select top (@MaxBulk) q.UID_DialogDBQueue, @SlotNumber from DialogDBQueue12 q with (readpast)  where q.UID_Task = @UID_TaskToStart   and q.Generation >= 0 select @RowsReadFromDBQueue = @@ROWCOUNT  if @RowsReadFromDBQueue > 0 begin13 insert into #UIDUmsetz(UID_DialogDBQueue, SlotNumber) select q.UID_DialogDBQueue, @SlotNumber from DialogDBQueue q with (readpast)  where UID_Task = @UID_TaskToStart14  and q.Generation >= 0 and not exists (select top 1 1 from #UIDUmsetz qe where qe.UID_DialogDBQueue = q.UID_DialogDBQueue ) select @RowsReadFromDBQueue15 += @@ROWCOUNT end  end  if @CountParameter = 1 begin insert into #UIDUmsetz(UID_DialogDBQueue, UID_Parameter, SlotNumber) select top (@MaxBulk) q.UID_DialogDBQueue16, q.Object, @SlotNumber from DialogDBQueue q with (readpast)  where q.UID_Task = @UID_TaskToStart and q.Object like @SearchPattern   and q.Generation >=17 0 select @RowsReadFromDBQueue = @@ROWCOUNT if @RowsReadFromDBQueue > 0 begin  insert into #UIDUmsetz (UID_DialogDBQueue, UID_Parameter, SlotNumber) select18 distinct q.UID_DialogDBQueue, q.object, @SlotNumber  from DialogDBQueue q with (readpast) join #UIDUmsetz p on p.UID_Parameter = q.object  where q.UID_Task19 = @UID_TaskToStart  and q.Generation >= 0 and not exists (select top 1 1 from #UIDUmsetz qe where qe.UID_DialogDBQueue = q.UID_DialogDBQueue ) select 20@RowsReadFromDBQueue += @@ROWCOUNT end  end  if @CountParameter = 2 begin insert into #UIDUmsetz(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, SlotNumber21) select top (@MaxBulk) q.UID_DialogDBQueue, q.Object, q.SubObject, @SlotNumber from DialogDBQueue q with (readpast)  where q.UID_Task = @UID_TaskToStart22 and q.Object like @SearchPattern  and q.Generation >= 0 select @RowsReadFromDBQueue = @@ROWCOUNT if @RowsReadFromDBQueue > 0 begin  insert into #UIDUmsetz23(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, SlotNumber) select distinct q.UID_DialogDBQueue, q.object, q.subobject, @SlotNumber  from DialogDBQueue24 q with (readpast ) join #UIDUmsetz p on p.UID_Parameter = q.object   and p.UID_SubParameter = q.Subobject where q.UID_Task = @UID_TaskToStart  and q.Generation25 >= 0 and not exists (select top 1 1 from #UIDUmsetz qe where qe.UID_DialogDBQueue = q.UID_DialogDBQueue ) select @RowsReadFromDBQueue += @@ROWCOUNT end26  end    if exists (select top 1 1 from QBMDBQueueCurrent with (readpast) where SlotNumber = @SlotNumber ) begin select @SlotNumberSource = @SlotNumber27 select @SlotnumberTarget = dbo.QBM_FGIDBQueueSlotResetType('Garbage01') delete @DBQueueToMove  insert into @DBQueueToMove(UID_DialogDBQueue) select cul.UID_DialogDBQueue28 from QBMDBQueueCurrent cul with (readpast) where cul.SlotNumber = @SlotNumber exec @RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove, @SlotNumberSource29, @SlotnumberTarget              if @DebugSwitch > 0 begin select @Message = 'Slot ausgeputzt ' exec QBM_PJournal @message , @@procid, 'D', @Debuglevel30 end end  if @DebugSwitch > 0 begin select @Message = concat('@RowsReadFromDBQueue ', trim(str(@RowsReadFromDBQueue)) , ' sätze ' , ' Slot #', trim(str31(@SlotNumber))) exec QBM_PJournal @message , @@procid, 'T', @DebugLevel end if @RowsReadFromDBQueue = 0 begin goto endlabel end  select @RowsFilledIntoCurrent32 = @RowsReadFromDBQueue          select @SQLMoveCmd = '33 				insert into QBMDBQueueCurrent 34					(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, SlotNumber, UID_Task, GenProcID, Generation, StartedAt  )35				select q.UID_DialogDBQueue, q.Object, q.SubObject, u.SlotNumber, q.UID_Task, q.GenProcID , q.Generation, getutcdate() 36					from DialogDBQueue q /*with (readpast) */ join #UIDUmsetz u on q.UID_DialogDBQueue = u.UID_DialogDBQueue37				-- noch ein Hosenträger, den wir erst mal weglassen:38				--										left outer join QBMDBQueueCurrent cu with (readpast) on cu.UID_DialogDBQueue = q.UID_DialogDBQueue39				--where cu.UID_DialogDBQueue is null4041				; update DialogDBQueue 42					set Generation = -143				 FROM DialogDBQueue q join #UIDUmsetz u on q.UID_DialogDBQueue = u.UID_DialogDBQueue44				-- noch ein Hosenträger, den wir erst mal weglassen:45									--join QBMDBQueueCurrent cu with (readpast) on cu.UID_DialogDBQueue = q.UID_DialogDBQueue46					-- falls Wiederholversuche anfallen47				 --where q.Generation >= 048				 '49 BEGIN TRY exec @RowsFilledIntoCurrent = QBM_PExecuteSQLWithRetry_LLP @SQLMoveCmd , @LockTimeout_ms = 500  , @MaxWaitTimeForLock_s = 8.0  , @ProcIDForJournal50 = null  , @HandleErrorSilent = 1   , @DeadlockPriority = 0 , @ExecuteWithTransact = 1  , @ErrorInExecute = @FehlerImExec output  select @WasNoSuccess 51= @FehlerImExec END TRY BEGIN CATCH select @WasNoSuccess = 1 exec QBM_PSessionErrorClean END CATCH                                                 END 52TRY BEGIN CATCH select @WasNoSuccess = 1 END CATCH BEGIN TRY truncate table #UIDUmsetz END TRY BEGIN CATCH END CATCH BEGIN TRY  if @@TRANCOUNT > 0 begin53 rollback transaction select @WasNoSuccess = 1 end END TRY BEGIN CATCH  select @WasNoSuccess = 1 END CATCH   endLabel: if @WasNoSuccess = 1 begin exec 54QBM_PJournal '@WasNoSuccess = 1', @@procid, 'T', @DebugLevel end return end 55