dbo.QBM_PForeignKeyWrongRepair

1   create   procedure QBM_PForeignKeyWrongRepair (@ChildTablePattern varchar(32) = '%' , @ChildColumnPattern varchar(32) = '%' , @ParentTablePattern
2 varchar(32) = '%' , @ParentColumnPattern varchar(32) = '%' , @RepairOnlyNonExistingModule bit = 0  , @OwnerOfParent varchar(3) = '%'  , @RepairFromReport
3 bit = 0   , @FilterByModuleGuidAllowed bit = 0    , @CheckOutsideValues bit = 0   , @MasterMigMode bit = 0  ) as begin declare @ParentTable nvarchar(64
4), @ChildTable nvarchar(64), @ParentColumn nvarchar(64), @ChildColumn nvarchar(64), @RepairMethod nvarchar(16) declare @ParentTableBase varchar(64) declare
5 @ParentRestriction nvarchar(16) declare @isNullableColumn nvarchar(10) declare @isPKMember bit declare @changed bit declare @changedTotal bit declare 
6@CountItems int declare @CmdWork nvarchar(max) declare @CmdDelete nvarchar(max) declare @CmdSetNull nvarchar(max) declare @msg nvarchar(1000) declare @IsDynamicFK
7 int declare @HasXObjectKey bit declare @ChangesForeign int = 0  declare @DebugSwitch int = 0 declare @ElementCount int declare @ElementIndex int declare
8 @ElementLast int declare @ElementBufferMulti QBM_YCursorBuffer declare @ElementCountOutsideValues int declare @ElementIndexOutsideValues int declare @ElementLastOutsideValues
9 int declare @ElementBufferOutsideValues QBM_YCursorBuffer declare @SQLOutsideValues nvarchar(max) declare @WerOutsideValues QBM_YParameterList SET XACT_ABORT
10 OFF BEGIN TRY set nocount on  drop table if exists #TablesForOutside  CREATE TABLE #TablesForOutside ( KeyPattern varchar(138) collate database_default
11 not null primary key ) insert into #TablesForOutside(KeyPattern ) select '<Key><T>' + t.TableName + '</T>' from DialogTable t  if @RepairFromReport = 
120  begin exec QBM_PForeignKeyEmptySetNull @ChildTablePattern end declare @ModCheck38X nvarchar(max) = ' dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.XObjectKey) '
13 declare @ModCheck38K nvarchar(max) = ' case dbo.QBM_FCVGUIDToModuleOwner(@ChildTable.@Childcolumn)  when ''ZZZ'' then left(@ChildTable.@Childcolumn, 3) else dbo.QBM_FCVGUIDToModuleOwner(@ChildTable.@Childcolumn) end '
14 declare @ModCheck138 nvarchar(max) = ' dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@Childcolumn) ' select @cmdDelete = concat('
15if exists (select top 1 1
16			from @ChildTable with (readpast)
17%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue
18			where not exists (select top 1 1
19								 from @ParentTable p  with (readpast)
20								where p.@Parentcolumn = @ChildTable.@Childcolumn
21							) 
22			 and @ChildTable.@Childcolumn > '' ''
23%Mamig%		 and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 
24%dyn%		 and @ChildTable.@Childcolumn like ''<Key><T>@ParentTable</T>%'' 
25'
26, case when @OwnerOfParent = '%' then '' else '
27%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''
28%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''
29'
30 end, '
31		)
32begin
33%Xori%	update @ChildTable 
34%Xori%		set XOrigin = 0
35%Xori%	  from @ChildTable 
36%Xori%%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue
37%Xori%		where not exists (select top 1 1
38%Xori%							 from @ParentTable p  with (readpast)
39%Xori%							where p.@Parentcolumn = @ChildTable.@Childcolumn
40%Xori%						) 
41%Xori%	-- und der Childschlssel ist nicht leer
42%Xori%		 and @ChildTable.@Childcolumn > '' ''
43%Xori%%dyn%		 and @ChildTable.@Childcolumn like ''<Key><T>@ParentTable</T>%'' 
44%Xori%%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0
45'
46, case when @OwnerOfParent = '%' then '' else '
47%Xori%%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''
48%Xori%%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''
49'
50 end, '
51
52	delete /* Platzhalter, keine Variable */ @ChildTable
53		from @ChildTable
54%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue
55		where not exists (select top 1 1
56							 from @ParentTable p   with (readpast)
57							where p.@Parentcolumn = @ChildTable.@Childcolumn
58						) 
59	-- und der Childschlssel ist nicht leer
60		 and @ChildTable.@Childcolumn > '' ''
61%dyn%		 and @ChildTable.@Childcolumn like ''<Key><T>@ParentTable</T>%'' 
62'
63, case when @OwnerOfParent = '%' then '' else '
64%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''
65%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''
66'
67 end, '
68
69%Mod%	and dbo.QBM_FGIModuleExists(%modcheck%) = 0
70%Mamig%		and @ChildTable.XObjectKey like ''<Key><T>@ChildTable</T><P>[a-z][0-z][0-z]-%'' 
71end
72'
73 ) declare @cmdDeleteDynamicOutOfValid nvarchar(Max) = concat('
74if exists (select top 1 1
75			from @ChildTable  with (readpast)
76%report%					join QBMForeignKeyWrongReport fkwr on @ChildTable.@ChildColumn = fkwr.InvalidValue
77						left outer join (
78										select tp.TableName
79										from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn
80																	join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable