Back to OIM Explorer

dbo.QBM_PFDA25F6EA8E748702E6D119_2

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.625 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_FGIColumnExists source text reference
references source dbo.QBM_FGIPrimaryKeyName source text reference
references source dbo.QBM_FGISessionErrorRethrow source text reference
references source dbo.QBM_PGUIDReplaceLight source text reference
references source dbo.QBM_PGUIDReplaceLighter source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

dbo.QBM_PGUIDReplaceLighter

Complete Source

1CREATE PROCEDURE QBM_PFDA25F6EA8E748702E6D119_2(2  @StartTable varchar(30)3)4AS5BEGIN6  SET XACT_ABORT OFF7  BEGIN TRY8    DECLARE @cmd nvarchar(max)9    DECLARE @ObjectKeysToReplace QBM_YParameterList10    DECLARE @ElementBuffer QBM_YCursorBuffer11    DECLARE @ElementCount int12    DECLARE @ElementIndex int13    DECLARE @TableName varchar(30)14    DECLARE @ColumnName varchar(30)15    DECLARE @DebugSwitch int = 016    INSERT INTO @ElementBuffer(ContentFull,17    Ident1,18    Ident2)19    SELECT20      CONCAT(convert(nvarchar(max), ''),21      '22			select ''',23      t.TableName,24      ''', ''',25      c.ColumnName,26      '''27				 where exists (28								select top 1 129								from ',30      t.TableName,31      '32								where ',33      c.ColumnName,34      ' like ''<Key><T>',35      @StartTable,36      '</T><P>%''37							)3839				 '),40      t.TableName,41      c.ColumnName42    FROM DialogColumn c43    JOIN DialogTable t44      ON c.UID_DialogTable = t.UID_DialogTable AND t.TableType IN('B',45    'T')46    LEFT47    OUTER48    JOIN DialogValidDynamicRef r49      ON r.UID_DialogColumn = c.UID_DialogColumn50    WHERE51      c.IsDynamicFK = 1 AND r.UID_DialogColumn IS NULL52    SELECT @ElementCount = @@ROWCOUNT53    SELECT @ElementIndex = 154    WHILE @ElementIndex <= @ElementCount55    BEGIN56      SELECT57        TOP 1 @cmd = bu.ContentFull,58        @TableName = bu.Ident1,59        @ColumnName = bu.Ident260      FROM @ElementBuffer bu61      WHERE62        bu.ElementIndex = @ElementIndex63      IF @DebugSwitch > 064      BEGIN65        print @cmd66      END67      INSERT INTO @ObjectKeysToReplace(Parameter1,68      Parameter2)69      EXEC sp_executesql @cmd70      IF @@ROWCOUNT > 071      BEGIN72        IF @DebugSwitch > 073        BEGIN74          print 'wird ausgewertet'75        END76      END77      SELECT @ElementIndex += 178    END79    INSERT INTO @ObjectKeysToReplace(Parameter1,80    Parameter2)81    SELECT82      x.Tablename,83      x.Columnname84    FROM(85    SELECT86      'QBMTaggedChange' AS Tablename,87      'ObjectKey' AS Columnname88    UNION89    SELECT90      'QBMBufferConfig',91      'ObjectKeyOfRow'92    UNION93    SELECT94      tr.TableName,95      c.ColumnName96    FROM DialogValidDynamicRef r97    JOIN DialogTable t98      ON r.UID_DialogTableReference = t.UID_DialogTable99    JOIN DialogColumn c100      ON r.UID_DialogColumn = c.UID_DialogColumn101    JOIN DialogTable tr102      ON c.UID_DialogTable = tr.UID_DialogTable103    WHERE104      t.TableName = @StartTable) AS x105    WHERE106      NOT EXISTS(107    SELECT TOP 1 1108    FROM @ObjectKeysToReplace e109    WHERE110      e.Parameter1 = x.Tablename AND e.Parameter2 = x.Columnname)111    INSERT INTO #FinaleStatements(ContentFull)112    SELECT x.SQLStatement113    FROM(114    SELECT115      1000 AS Sortorder,116      CONCAT('update ', @StartTable, '117		set ', dbo.QBM_FGIPrimaryKeyName(@StartTable, 1), '  = z.PK_New118			, XObjectKey = z.XO_New ',119    CASE dbo.QBM_FGIColumnExists(@Starttable, 'XDateUpdated')120      WHEN 1 THEN121    ', XDateUpdated = GETUTCDATE(), XUserUpdated = ''QBM_PGUIDReplaceLighter'' '122    ELSE ''123    END, '	124	from ', @StartTable, ' t join #Zwischenstand z on t.', dbo.QBM_FGIPrimaryKeyName(@StartTable,125    1), ' = z.PK_Old126									where not exists (select top 1 1127														from ', @StartTable, ' te128														where te.XObjectKey = z.XO_New129													)130				'131    ) AS SQLStatement132    UNION133    SELECT134      1 AS Sortorder,135      CONCAT('update ', r.Parameter1, '136										set ', r.Parameter2, ' = y.XO_New ', CASE dbo.QBM_FGIColumnExists(r.Parameter1,137      'XDateUpdated')138      WHEN 1 THEN139      ', XDateUpdated = GETUTCDATE(), XUserUpdated = ''QBM_PGUIDReplaceLighter'' '140    ELSE ''141    END, '142									from ', r.Parameter1, ' t join #Zwischenstand y on t.', r.Parameter2, ' = y.XO_Old143									where not exists (select top 1 1144														from '145    , r.Parameter1, ' te146														where te.', r.Parameter2, ' = y.XO_New147													)') AS SQLStatement148    FROM @ObjectKeysToReplace r) AS x149    ORDER BY x.Sortorder150  END TRY151  BEGIN CATCH152    EXEC QBM_PSessionErrorAdd DEFAULT153    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()154    RAISERROR(@Rethrow,155    18,156    1)157      WITH NOWAIT158  END CATCH159  endLabel:160  RETURN161END

Open raw exported source

1 create   procedure QBM_PFDA25F6EA8E748702E6D119_2 (@StartTable varchar(30) ) as begin SET XACT_ABORT OFF BEGIN TRY declare @cmd nvarchar(max) declare2 @ObjectKeysToReplace QBM_YParameterList     declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @TableName3 varchar(30) declare @ColumnName varchar(30) declare @DebugSwitch int = 0  insert into @ElementBuffer(ContentFull, Ident1, Ident2) select concat( convert4(nvarchar(max), ''), '5			select ''', t.TableName ,''', ''', c.ColumnName, '''6				 where exists (7								select top 1 18								from ', t.TableName9 ,'10								where ', c.ColumnName , ' like ''<Key><T>', @StartTable , '</T><P>%''11							)1213				 ' )  , t.TableName, c.ColumnName from DialogColumn14 c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable and t.TableType in ('B', 'T') left outer join DialogValidDynamicRef r on r.UID_DialogColumn15 = c.UID_DialogColumn where c.IsDynamicFK = 1 and r.UID_DialogColumn is null select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex16 <= @ElementCount begin select top 1 @cmd = bu.ContentFull , @TableName = bu.Ident1 , @ColumnName = bu.Ident2 from @ElementBuffer bu where bu.ElementIndex17 = @ElementIndex if @DebugSwitch > 0 begin print @cmd end   insert into @ObjectKeysToReplace(Parameter1, Parameter2) exec sp_executesql @cmd if @@ROWCOUNT18 > 0 begin   if @DebugSwitch > 0 begin print 'wird ausgewertet' end end  select @ElementIndex += 1 end  insert into @ObjectKeysToReplace(Parameter1, Parameter219) select x.Tablename, x.Columnname from ( select 'QBMTaggedChange' as Tablename, 'ObjectKey' as Columnname union select 'QBMBufferConfig', 'ObjectKeyOfRow'20 union  select tr.TableName, c.ColumnName from DialogValidDynamicRef r join DialogTable t on r.UID_DialogTableReference = t.UID_DialogTable join DialogColumn21 c on r.UID_DialogColumn = c.UID_DialogColumn join DialogTable tr on c.UID_DialogTable = tr.UID_DialogTable where t.TableName = @StartTable ) as x where22 not exists (select top 1 1 from @ObjectKeysToReplace e where e.Parameter1 = x.Tablename and e.Parameter2 = x.Columnname ) insert into #FinaleStatements23(ContentFull) select x.SQLStatement from ( select 1000 as Sortorder, CONCAT('update ', @StartTable,'24		set ', dbo.QBM_FGIPrimaryKeyName(@StartTable,1)25,'  = z.PK_New26			, XObjectKey = z.XO_New ' ,case dbo.QBM_FGIColumnExists(@Starttable, 'XDateUpdated') when 1 then ', XDateUpdated = GETUTCDATE(), XUserUpdated = ''QBM_PGUIDReplaceLighter'' '27 else '' end, '	28	from ', @StartTable,' t join #Zwischenstand z on t.', dbo.QBM_FGIPrimaryKeyName(@StartTable,1),' = z.PK_Old29									where not exists (select top 1 130														from '31, @StartTable,' te32														where te.XObjectKey = z.XO_New33													)34				') as SQLStatement union select 1 as Sortorder, concat( 'update '35, r.Parameter1,'36										set ', r.Parameter2,' = y.XO_New ' ,case dbo.QBM_FGIColumnExists( r.Parameter1, 'XDateUpdated') when 1 then ', XDateUpdated = GETUTCDATE(), XUserUpdated = ''QBM_PGUIDReplaceLighter'' '37 else '' end, '38									from ', r.Parameter1,' t join #Zwischenstand y on t.', r.Parameter2,' = y.XO_Old39									where not exists (select top 1 140														from '41, r.Parameter1,' te42														where te.', r.Parameter2,' = y.XO_New43													)' ) as SQLStatement  from @ObjectKeysToReplace r ) as x order by44 x.Sortorder END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow45, 18, 1) WITH NOWAIT END CATCH endLabel: return end 46