dbo.QBM_PFDA25F6EA8E748702E6D119_1

1   create   procedure QBM_PFDA25F6EA8E748702E6D119_1 (@StartTable varchar(30) , @StartColumn varchar(30) ) as begin declare @TableName varchar(30
2) declare @ColumnName varchar(30) declare @sqlcmd nvarchar(max) declare @Statements QBM_YParameterlist declare @UID_StatementCode varchar(38) declare @DebugSwitch
3 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_Old
8			') as ContentFull  from QBM_VQBMRelation r join DialogColumn c on
9 r.UID_ChildColumn = c.UID_DialogColumn join DialogTable t on c.UID_DialogTable = t.UID_DialogTable and t.TableType in ('B', 'T') join QBM_VQBMRelation
10 ra on r.UID_DialogTableChild = ra.UID_DialogTableChild and ra.UID_QBMRelation <> r.UID_QBMRelation join DialogColumn xc on xc.UID_DialogTable = c.UID_DialogTable
11 and xc.ColumnName = 'XObjectKey' where r.ParentTable = @StartTable and r.ChildTable <> @StartTable and c.IsPKMember = 1 ) as x  select @UID_StatementCode
12 = '#' while @UID_StatementCode > ' ' begin select @UID_StatementCode = null select top 1 @TableName = s.Parameter2 , @ColumnName = s.Parameter3 , @sqlcmd
13 = s.ContentFull , @UID_StatementCode = s.Parameter1 from @Statements s if @UID_StatementCode is null begin continue end if @Debugswitch > 0 begin print
14 @SQLCmd  end exec sp_executesql @SQLCmd delete  @Statements  from @Statements s where s.Parameter1 = @UID_StatementCode end  if @Debugswitch > 0 begin
15 print 'nach Auffüllung m:n Xobjectkeys '  end   insert into #finaleStatements (ContentFull) select x.StatementCode  from ( select replace(replace(replace
16(replace(v.StamentPattern , '#tabelle#', s.TableName) , '#spalte#', s.ColumnName) , '#otherPK#', s.OtherPK) , '#xwerte#', case dbo.QBM_FGIColumnExistsInSchema
17(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 StatementType
19, '' as OtherPK from QBM_VQBMRelation r join DialogColumn c on r.UID_ChildColumn = c.UID_DialogColumn where r.ParentTable = @StartTable and c.IsPKMember
20 = 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 DialogTable
22 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_QBMRelation
23 left outer join DialogColumn cx on cx.UID_DialogTable = c.UID_DialogTable and cx.columnName = 'XObjectKey' where r.ParentTable = @StartTable and c.IsPKMember
24 = 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 tr
28 on c.UID_DialogTable = tr.UID_DialogTable where t.TableName = @StartTable  union select 1 as Sortorder, tr.TableName, c.ColumnName, 'U-XO', '' as OtherPK
29 from DialogValidDynamicRef r join DialogTable t on r.UID_DialogTableReference = t.UID_DialogTable join DialogColumn c on r.UID_DialogColumn = c.UID_DialogColumn
30 join DialogTable tr on c.UID_DialogTable = tr.UID_DialogTable where t.TableName in ( select r.ChildTable as TableName from QBM_VQBMRelation r join DialogColumn
31 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_QBMRelation
32 where r.ParentTable = @StartTable and c.IsPKMember = 1 )  union select 1 as Sortorder, t.TableName, c.ColumnName, 'U-XO', '' as OtherPK from DialogColumn
33 c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable left outer join DialogValidDynamicRef r on r.UID_DialogColumn = c.UID_DialogColumn where
34 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.COLUMNS
35 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_Old
38									'
39) , ('UE-PK', 'update #tabelle# 
40										set #spalte# = y.PK_New #xwerte#
41									from #tabelle# t join #Zwischenstand y on t.#spalte# = y.PK_Old
42									where not exists (select top 1 1
43														from #tabelle# te
44														where te.#spalte# = y.PK_New
45														 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_Old
51									where not exists (select top 1 1
52														from #tabelle# te
53														where te.#spalte# = y.PK_New
54														 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_Old
59									where not exists (select top 1 1
60														from #tabelle# te
61														where te.#spalte# = y.PK_New
62													)'
63) , ('U-XO', 'update #tabelle# 
64									set #spalte# = y.XO_New #xwerte#
65									from #tabelle# x join #Zwischenstand y on x.#spalte# = y.XO_Old
66									'
67) , ('UE-XO', 'update #tabelle# 
68										set #spalte# = y.XO_New #xwerte#
69									from #tabelle# t join #Zwischenstand y on t.#spalte# = y.XO_Old
70									where not exists (select top 1 1
71														from #tabelle# te
72														where te.#spalte# = y.XO_New
73													)'
74) ) as v (StatementType, StamentPattern) on s.StatementType = v.StatementType ) as x order by x.Sortorder return end 
75