Back to OIM Explorer

dbo.QBM_PDialogProcessShrink_3

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.778 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_PExecuteSQLWithRetry_LLP source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

dbo.QBM_PProcessShrink

Complete Source

1CREATE PROCEDURE QBM_PDialogProcessShrink_3(2  @AnzahlGesamt int OUTPUT,3  @KeyPattern varchar(25) = '%'4)5AS6BEGIN7  DECLARE @CountItems int8  SELECT @AnzahlGesamt = 09  DECLARE @GenProcIDNew varchar(38),10  @GenProcIDOrigin varchar(38)11  DECLARE @ElementBuffer QBM_YCursorBuffer12  DECLARE @ElementCount int13  DECLARE @ElementIndex int14  DECLARE @Pattern nvarchar(max) = '		15		update DialogProcess 16			set ReadyForDeleteOrExport = 017			where GenProcID = ''@GenProcIDOrigin''18			 and ReadyForDeleteOrExport > 019		20		update DialogDBQueue             set GenProcID = ''@GenProcIDOrigin'' 21				from DialogDBQueue q22				where q.GenProcID = ''@GenProcIDNew''23				-- 2696224				and q.Generation >= 02526		update QBMDBQueueCurrent      set GenProcID = ''@GenProcIDOrigin'' where GenProcID = ''@GenProcIDNew''27		update DialogDeferredOperation   set GenProcID = ''@GenProcIDOrigin'' where GenProcID = ''@GenProcIDNew''28		update JobQueue                  set GenProcID = ''@GenProcIDOrigin'' where GenProcID = ''@GenProcIDNew''2930		update DialogProcessChain        set GenProcID = ''@GenProcIDOrigin'' where GenProcID = ''@GenProcIDNew''31		update DialogWatchOperation      set GenProcID = ''@GenProcIDOrigin'' where GenProcID = ''@GenProcIDNew''32		update JobHistory                set GenProcID = ''@GenProcIDOrigin'' where GenProcID = ''@GenProcIDNew''33'34  DECLARE @SQLCmdWithRetry nvarchar(max)35  SET XACT_ABORT OFF36  BEGIN TRY37    INSERT INTO @ElementBuffer(UID1,38    UID2)39    SELECT40      fi.GenProcIDNew,41      fi.GenProcIDOrigin42    FROM(43    SELECT44      GenProcIDOrigin,45      GenProcIDNew,46      COUNT(*) over(partition BY GenProcIDNew) AS AnzahlNew47    FROM DialogProcessSubstitute s48    WHERE49      s.GenProcIDNew LIKE @KeyPattern AND s.ReadyForDeleteOrExport = 0) AS fi50    WHERE51      fi.AnzahlNew = 152    SELECT @ElementCount = @@rowcount53    SELECT @ElementIndex = 154    WHILE @ElementIndex <= @ElementCount55    BEGIN56      SELECT57        TOP 1 @GenProcIDNew = bu.UID1,58        @GenProcIDOrigin = bu.UID259      FROM @ElementBuffer bu60      WHERE61        bu.ElementIndex = @ElementIndex62      SELECT63        @SQLCmdWithRetry = replace(replace(@Pattern, '@GenProcIDOrigin', @GenProcIDOrigin),64        '@GenProcIDNew',65        @GenProcIDNew)66      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLCmdWithRetry,67        @LockTimeout_ms = DEFAULT,68        @MaxWaitTimeForLock_s = DEFAULT,69        @ProcIDForJournal = DEFAULT,70        @HandleErrorSilent = 1,71        @ExecuteWithTransact = 072      UPDATE DialogProcessSubstitute73      SET ReadyForDeleteOrExport = 174      FROM DialogProcessSubstitute s75      WHERE76        s.GenProcIDNew = @GenProcIDNew AND s.ReadyForDeleteOrExport <> 177      SELECT @CountItems = @@rowcount78      SELECT @Anzahlgesamt = @Anzahlgesamt + @CountItems79      SELECT @ElementIndex += 180    END81  END TRY82  BEGIN CATCH83    EXEC QBM_PSessionErrorAdd DEFAULT84    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()85    RAISERROR(@Rethrow,86    18,87    1)88      WITH NOWAIT89  END CATCH90END

Open raw exported source

1  create   procedure QBM_PDialogProcessShrink_3 (@AnzahlGesamt int output , @KeyPattern varchar(25) = '%'  ) as begin declare @CountItems int  select2 @AnzahlGesamt = 0  declare @GenProcIDNew varchar(38), @GenProcIDOrigin varchar(38) declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int 3declare @ElementIndex int declare @Pattern nvarchar(max) = '		4		update DialogProcess 5			set ReadyForDeleteOrExport = 06			where GenProcID = ''@GenProcIDOrigin''7			 and ReadyForDeleteOrExport > 08		9		update DialogDBQueue             set GenProcID = ''@GenProcIDOrigin'' 10				from DialogDBQueue q11				where q.GenProcID = ''@GenProcIDNew''12				-- 2696213				and q.Generation >= 01415		update QBMDBQueueCurrent      set GenProcID = ''@GenProcIDOrigin'' where GenProcID = ''@GenProcIDNew''16		update DialogDeferredOperation   set GenProcID = ''@GenProcIDOrigin'' where GenProcID = ''@GenProcIDNew''17		update JobQueue                  set GenProcID = ''@GenProcIDOrigin'' where GenProcID = ''@GenProcIDNew''1819		update DialogProcessChain        set GenProcID = ''@GenProcIDOrigin'' where GenProcID = ''@GenProcIDNew''20		update DialogWatchOperation      set GenProcID = ''@GenProcIDOrigin'' where GenProcID = ''@GenProcIDNew''21		update JobHistory                set GenProcID = ''@GenProcIDOrigin'' where GenProcID = ''@GenProcIDNew''22'23 declare @SQLCmdWithRetry nvarchar(max) SET XACT_ABORT OFF BEGIN TRY insert into @ElementBuffer(UID1, UID2) select fi.GenProcIDNew, fi.GenProcIDOrigin 24from ( select GenProcIDOrigin, GenProcIDNew , COUNT(*) over (partition by GenProcIDNew) as AnzahlNew from DialogProcessSubstitute s where s.GenProcIDNew25 like @KeyPattern and s.ReadyForDeleteOrExport = 0  ) as fi where fi.AnzahlNew = 1 select @ElementCount = @@rowcount select @ElementIndex = 1 while @ElementIndex26 <= @ElementCount begin select top 1 @GenProcIDNew = bu.UID1 , @GenProcIDOrigin = bu.UID2 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex 27select @SQLCmdWithRetry = replace(replace(@Pattern , '@GenProcIDOrigin', @GenProcIDOrigin) , '@GenProcIDNew', @GenProcIDNew) exec QBM_PExecuteSQLWithRetry_LLP28 @SQLStatement = @SQLCmdWithRetry , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = default , @HandleErrorSilent = 1 29, @ExecuteWithTransact = 0   update DialogProcessSubstitute set ReadyForDeleteOrExport = 1 from DialogProcessSubstitute s where s.GenProcIDNew = @GenProcIDNew30 and s.ReadyForDeleteOrExport <> 1 select @CountItems = @@rowcount select @Anzahlgesamt = @Anzahlgesamt + @CountItems select @ElementIndex += 1 end  END31 TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT32 END CATCH end 33