Back to OIM Explorer

dbo.QBM_PFDA25F6EA8E748702E6D119_1

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.690 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_FCVStringToGUID source text reference
  • references source dbo.QBM_FGIColumnExists source text reference
  • references source dbo.QBM_FGIColumnExistsInSchema source text reference
  • references source dbo.QBM_PGUIDReplaceLight source text reference
  • references source dbo.QBM_PGUIDReplaceLighter source text reference

References

Referenced By

Complete Source

SQL213 lines
1CREATE PROCEDURE QBM_PFDA25F6EA8E748702E6D119_1(2  @StartTable varchar(30),3  @StartColumn varchar(30)4)5AS6BEGIN7  DECLARE @TableName varchar(30)8  DECLARE @ColumnName varchar(30)9  DECLARE @sqlcmd nvarchar(max)10  DECLARE @Statements QBM_YParameterlist11  DECLARE @UID_StatementCode varchar(38)12  DECLARE @DebugSwitch int = 013  INSERT INTO @Statements(ContentFull,14  Parameter1)15  SELECT16    x.ContentFull,17    dbo.QBM_FCVStringToGUID('',18    x.ContentFull)19  FROM(20  SELECT21    CONCAT('22		insert into #Zwischenstand (XO_Old, XO_New)23		select t.XObjectKey, replace(t.xobjectkey , y.PK_Old, y.PK_New)24			from ',25    r.ChildTable, ' t join #Zwischenstand y on t.', r.ChildColumn, ' = y.PK_Old26			') AS ContentFull27  FROM QBM_VQBMRelation r28  JOIN DialogColumn c29    ON r.UID_ChildColumn = c.UID_DialogColumn30  JOIN DialogTable t31    ON c.UID_DialogTable = t.UID_DialogTable AND t.TableType IN('B', 'T')32  JOIN QBM_VQBMRelation ra33    ON r.UID_DialogTableChild = ra.UID_DialogTableChild AND ra.UID_QBMRelation <> r.UID_QBMRelation34  JOIN DialogColumn xc35    ON xc.UID_DialogTable = c.UID_DialogTable AND xc.ColumnName = 'XObjectKey'36  WHERE37    r.ParentTable = @StartTable AND r.ChildTable <> @StartTable AND c.IsPKMember = 1) AS x38  SELECT @UID_StatementCode = '#'39  WHILE @UID_StatementCode > ' '40  BEGIN41    SELECT @UID_StatementCode = NULL42    SELECT43      TOP 1 @TableName = s.Parameter2,44      @ColumnName = s.Parameter3,45      @sqlcmd = s.ContentFull,46      @UID_StatementCode = s.Parameter147    FROM @Statements s48    IF @UID_StatementCode IS NULL49    BEGIN50      CONTINUE51    END52    IF @Debugswitch > 053    BEGIN54      print @SQLCmd55    END56    EXEC sp_executesql @SQLCmd57    DELETE @Statements58    FROM @Statements s59    WHERE60      s.Parameter1 = @UID_StatementCode61  END62  IF @Debugswitch > 063  BEGIN64    print 'nach Auffüllung m:n Xobjectkeys '65  END66  INSERT INTO #finaleStatements(ContentFull)67  SELECT x.StatementCode68  FROM(69  SELECT70    replace(replace(replace(replace(v.StamentPattern, '#tabelle#', s.TableName), '#spalte#', s.ColumnName),71    '#otherPK#', s.OtherPK), '#xwerte#', CASE dbo.QBM_FGIColumnExistsInSchema(s.TableName, 'XDateUpdated')72    WHEN 1 THEN73    ', XDateUpdated = getutcdate(), XUserUpdated =  ''QBM_PGUIDReplaceLighter'''74  ELSE ''75  END) AS StatementCode,76  s.TableName,77  s.ColumnName,78  s.Sortorder79  FROM(80  SELECT81    1 AS Sortorder, r.ChildTable AS TableName, r.ChildColumn AS ColumnName, 'U-PK' AS StatementType,82    '' AS OtherPK83  FROM QBM_VQBMRelation r84  JOIN DialogColumn c85    ON r.UID_ChildColumn = c.UID_DialogColumn86  WHERE87    r.ParentTable = @StartTable AND c.IsPKMember = 088  UNION89  SELECT90    1 AS Sortorder, r.ChildTable AS TableName, r.ChildColumn AS ColumnName, CASE91    WHEN cx.UID_DialogColumn IS NULL THEN92    'U-PK'93  ELSE 'UE-PK-M'94  END AS StatementType, ra.ChildColumn AS OtherPK95  FROM QBM_VQBMRelation r96  JOIN DialogColumn c97    ON r.UID_ChildColumn = c.UID_DialogColumn98  JOIN DialogTable t99    ON c.UID_DialogTable = t.UID_DialogTable100  JOIN QBM_VQBMRelation ra101    ON r.UID_DialogTableChild = ra.UID_DialogTableChild AND ra.UID_QBMRelation <> r.UID_QBMRelation102  LEFT103  OUTER104  JOIN DialogColumn cx105    ON cx.UID_DialogTable = c.UID_DialogTable AND cx.columnName = 'XObjectKey'106  WHERE107    r.ParentTable = @StartTable AND c.IsPKMember = 1108  UNION109  SELECT110    1000 AS Sortorder, @StartTable, @StartColumn, 'UE1-PK', '' AS OtherPK111  UNION112  SELECT113    999 AS Sortorder, @StartTable, 'XObjectKey', 'UE-XO', '' AS OtherPK114  UNION115  SELECT116    1 AS Sortorder, 'QBMTaggedChange', 'ObjectKey', 'U-XO', '' AS OtherPK117  UNION118  SELECT119    1 AS Sortorder, 'QBMBufferConfig', 'ObjectKeyOfRow', 'U-XO', '' AS OtherPK120  UNION121  SELECT122    1 AS Sortorder, tr.TableName, c.ColumnName, 'U-XO', '' AS OtherPK123  FROM DialogValidDynamicRef r124  JOIN DialogTable t125    ON r.UID_DialogTableReference = t.UID_DialogTable126  JOIN DialogColumn c127    ON r.UID_DialogColumn = c.UID_DialogColumn128  JOIN DialogTable tr129    ON c.UID_DialogTable = tr.UID_DialogTable130  WHERE131    t.TableName = @StartTable132  UNION133  SELECT134    1 AS Sortorder, tr.TableName, c.ColumnName, 'U-XO', '' AS OtherPK135  FROM DialogValidDynamicRef r136  JOIN DialogTable t137    ON r.UID_DialogTableReference = t.UID_DialogTable138  JOIN DialogColumn c139    ON r.UID_DialogColumn = c.UID_DialogColumn140  JOIN DialogTable tr141    ON c.UID_DialogTable = tr.UID_DialogTable142  WHERE143    t.TableName IN(144  SELECT r.ChildTable AS TableName145  FROM QBM_VQBMRelation r146  JOIN DialogColumn c147    ON r.UID_ChildColumn = c.UID_DialogColumn148  JOIN QBM_VQBMRelation ra149    ON r.UID_DialogTableChild = ra.UID_DialogTableChild AND ra.UID_QBMRelation <> r.UID_QBMRelation150  WHERE151    r.ParentTable = @StartTable AND c.IsPKMember = 1)152  UNION153  SELECT154    1 AS Sortorder, t.TableName, c.ColumnName, 'U-XO', '' AS OtherPK155  FROM DialogColumn c156  JOIN DialogTable t157    ON c.UID_DialogTable = t.UID_DialogTable158  LEFT159  OUTER160  JOIN DialogValidDynamicRef r161    ON r.UID_DialogColumn = c.UID_DialogColumn162  WHERE163    c.IsDynamicFK = 1 AND r.UID_DialogColumn IS NULL) s164  JOIN DialogTable te165    ON te.TableName = s.TableName AND te.TableType IN('B', 'T')166  JOIN INFORMATION_SCHEMA.COLUMNS ic167    WITH(readpast)168    ON ic.TABLE_NAME = s.TableName AND ic.COLUMN_NAME = s.ColumnName169  JOIN(170  VALUES('U-PK', 'update #tabelle# 171									set #spalte# = y.PK_New #xwerte#172									from #tabelle# x join #Zwischenstand y on x.#spalte# = y.PK_Old173									'174  ),('UE-PK', 'update #tabelle# 175										set #spalte# = y.PK_New #xwerte#176									from #tabelle# t join #Zwischenstand y on t.#spalte# = y.PK_Old177									where not exists (select top 1 1178														from #tabelle# te179														where te.#spalte# = y.PK_New180														 and te.#otherPK# = t.#otherPK#181													)'182  ),('UE-PK-M', 'update #tabelle# 183										set #spalte# = y.PK_New #xwerte#184										, XobjectKey = replace(t.XObjectkey, y.PK_Old, y.PK_New)185									from #tabelle# t join #Zwischenstand y on t.#spalte# = y.PK_Old186									where not exists (select top 1 1187														from #tabelle# te188														where te.#spalte# = y.PK_New189														 and te.#otherPK# = t.#otherPK#190													)'191  ),('UE1-PK', 'update #tabelle# 192										set #spalte# = y.PK_New #xwerte#193									from #tabelle# t join #Zwischenstand y on t.#spalte# = y.PK_Old194									where not exists (select top 1 1195														from #tabelle# te196														where te.#spalte# = y.PK_New197													)'198  ),('U-XO', 'update #tabelle# 199									set #spalte# = y.XO_New #xwerte#200									from #tabelle# x join #Zwischenstand y on x.#spalte# = y.XO_Old201									'202  ),('UE-XO', 'update #tabelle# 203										set #spalte# = y.XO_New #xwerte#204									from #tabelle# t join #Zwischenstand y on t.#spalte# = y.XO_Old205									where not exists (select top 1 1206														from #tabelle# te207														where te.#spalte# = y.XO_New208													)'209  )) AS v(StatementType, StamentPattern)210    ON s.StatementType = v.StatementType) AS x211  ORDER BY x.Sortorder212  RETURN213END
Open raw exported source
SQL · Raw75 lines
1   create   procedure QBM_PFDA25F6EA8E748702E6D119_1 (@StartTable varchar(30) , @StartColumn varchar(30) ) as begin declare @TableName varchar(302) declare @ColumnName varchar(30) declare @sqlcmd nvarchar(max) declare @Statements QBM_YParameterlist declare @UID_StatementCode varchar(38) declare @DebugSwitch3 int = 0 insert into @Statements(ContentFull, Parameter1) select x.ContentFull, dbo.QBM_FCVStringToGUID('', x.ContentFull) from ( select concat('4		insert into #Zwischenstand (XO_Old, XO_New)5		select t.XObjectKey, replace(t.xobjectkey , y.PK_Old, y.PK_New)6			from '7 , r.ChildTable , ' t join #Zwischenstand y on t.' , r.ChildColumn , ' = y.PK_Old8			') as ContentFull  from QBM_VQBMRelation r join DialogColumn c on9 r.UID_ChildColumn = c.UID_DialogColumn join DialogTable t on c.UID_DialogTable = t.UID_DialogTable and t.TableType in ('B', 'T') join QBM_VQBMRelation10 ra on r.UID_DialogTableChild = ra.UID_DialogTableChild and ra.UID_QBMRelation <> r.UID_QBMRelation join DialogColumn xc on xc.UID_DialogTable = c.UID_DialogTable11 and xc.ColumnName = 'XObjectKey' where r.ParentTable = @StartTable and r.ChildTable <> @StartTable and c.IsPKMember = 1 ) as x  select @UID_StatementCode12 = '#' while @UID_StatementCode > ' ' begin select @UID_StatementCode = null select top 1 @TableName = s.Parameter2 , @ColumnName = s.Parameter3 , @sqlcmd13 = s.ContentFull , @UID_StatementCode = s.Parameter1 from @Statements s if @UID_StatementCode is null begin continue end if @Debugswitch > 0 begin print14 @SQLCmd  end exec sp_executesql @SQLCmd delete  @Statements  from @Statements s where s.Parameter1 = @UID_StatementCode end  if @Debugswitch > 0 begin15 print 'nach Auffüllung m:n Xobjectkeys '  end   insert into #finaleStatements (ContentFull) select x.StatementCode  from ( select replace(replace(replace16(replace(v.StamentPattern , '#tabelle#', s.TableName) , '#spalte#', s.ColumnName) , '#otherPK#', s.OtherPK) , '#xwerte#', case dbo.QBM_FGIColumnExistsInSchema17(s.TableName, 'XDateUpdated') when 1 then ', XDateUpdated = getutcdate(), XUserUpdated =  ''QBM_PGUIDReplaceLighter''' else '' end ) as StatementCode ,18 s.TableName, s.ColumnName , s.Sortorder from (  select 1 as Sortorder, r.ChildTable as TableName, r.ChildColumn as ColumnName, 'U-PK' as StatementType19, '' as OtherPK from QBM_VQBMRelation r join DialogColumn c on r.UID_ChildColumn = c.UID_DialogColumn where r.ParentTable = @StartTable and c.IsPKMember20 = 0 union select 1 as Sortorder, r.ChildTable as TableName, r.ChildColumn as ColumnName , case when cx.UID_DialogColumn is null then 'U-PK' else 'UE-PK-M'21  end as StatementType , ra.ChildColumn as OtherPK from QBM_VQBMRelation r join DialogColumn c on r.UID_ChildColumn = c.UID_DialogColumn join DialogTable22 t on c.UID_DialogTable = t.UID_DialogTable join QBM_VQBMRelation ra on r.UID_DialogTableChild = ra.UID_DialogTableChild and ra.UID_QBMRelation <> r.UID_QBMRelation23 left outer join DialogColumn cx on cx.UID_DialogTable = c.UID_DialogTable and cx.columnName = 'XObjectKey' where r.ParentTable = @StartTable and c.IsPKMember24 = 1          union  select 1000 as Sortorder, @StartTable, @StartColumn, 'UE1-PK', '' as OtherPK  union select 999 as Sortorder, @StartTable, 'XObjectKey'25, 'UE-XO', '' as OtherPK union select 1 as Sortorder, 'QBMTaggedChange', 'ObjectKey', 'U-XO', '' as OtherPK union select 1 as Sortorder, 'QBMBufferConfig'26, 'ObjectKeyOfRow', 'U-XO', '' as OtherPK union  select 1 as Sortorder, tr.TableName, c.ColumnName, 'U-XO', '' as OtherPK from DialogValidDynamicRef r 27join DialogTable t on r.UID_DialogTableReference = t.UID_DialogTable join DialogColumn c on r.UID_DialogColumn = c.UID_DialogColumn join DialogTable tr28 on c.UID_DialogTable = tr.UID_DialogTable where t.TableName = @StartTable  union select 1 as Sortorder, tr.TableName, c.ColumnName, 'U-XO', '' as OtherPK29 from DialogValidDynamicRef r join DialogTable t on r.UID_DialogTableReference = t.UID_DialogTable join DialogColumn c on r.UID_DialogColumn = c.UID_DialogColumn30 join DialogTable tr on c.UID_DialogTable = tr.UID_DialogTable where t.TableName in ( select r.ChildTable as TableName from QBM_VQBMRelation r join DialogColumn31 c on r.UID_ChildColumn = c.UID_DialogColumn join QBM_VQBMRelation ra on r.UID_DialogTableChild = ra.UID_DialogTableChild and ra.UID_QBMRelation <> r.UID_QBMRelation32 where r.ParentTable = @StartTable and c.IsPKMember = 1 )  union select 1 as Sortorder, t.TableName, c.ColumnName, 'U-XO', '' as OtherPK from DialogColumn33 c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable left outer join DialogValidDynamicRef r on r.UID_DialogColumn = c.UID_DialogColumn where34 c.IsDynamicFK = 1 and r.UID_DialogColumn is null ) s join DialogTable te on te.TableName = s.TableName and te.TableType in ('B', 'T')  join INFORMATION_SCHEMA.COLUMNS35 ic with (readpast) on ic.TABLE_NAME = s.TableName and ic.COLUMN_NAME = s.ColumnName join ( values ('U-PK', 'update #tabelle# 36									set #spalte# = y.PK_New #xwerte#37									from #tabelle# x join #Zwischenstand y on x.#spalte# = y.PK_Old38									'39) , ('UE-PK', 'update #tabelle# 40										set #spalte# = y.PK_New #xwerte#41									from #tabelle# t join #Zwischenstand y on t.#spalte# = y.PK_Old42									where not exists (select top 1 143														from #tabelle# te44														where te.#spalte# = y.PK_New45														 and te.#otherPK# = t.#otherPK#46													)'47) , ('UE-PK-M', 'update #tabelle# 48										set #spalte# = y.PK_New #xwerte#49										, XobjectKey = replace(t.XObjectkey, y.PK_Old, y.PK_New)50									from #tabelle# t join #Zwischenstand y on t.#spalte# = y.PK_Old51									where not exists (select top 1 152														from #tabelle# te53														where te.#spalte# = y.PK_New54														 and te.#otherPK# = t.#otherPK#55													)'56) , ('UE1-PK', 'update #tabelle# 57										set #spalte# = y.PK_New #xwerte#58									from #tabelle# t join #Zwischenstand y on t.#spalte# = y.PK_Old59									where not exists (select top 1 160														from #tabelle# te61														where te.#spalte# = y.PK_New62													)'63) , ('U-XO', 'update #tabelle# 64									set #spalte# = y.XO_New #xwerte#65									from #tabelle# x join #Zwischenstand y on x.#spalte# = y.XO_Old66									'67) , ('UE-XO', 'update #tabelle# 68										set #spalte# = y.XO_New #xwerte#69									from #tabelle# t join #Zwischenstand y on t.#spalte# = y.XO_Old70									where not exists (select top 1 171														from #tabelle# te72														where te.#spalte# = y.XO_New73													)'74) ) as v (StatementType, StamentPattern) on s.StatementType = v.StatementType ) as x order by x.Sortorder return end 75