dbo.QBM_PTElementsReferenced_I

1  create   procedure QBM_PTElementsReferenced_I (@StartElements QBM_YParameterList readonly  , @SameModule bit = 1 , @TopDown bit = 1   , @ExcludeDS
2 bit = 0  ) as begin declare @Lauf int declare @SQLCmdInsert nvarchar(max) declare @SQLCmdUpdate nvarchar(max) declare @SQLCmdPre nvarchar(max) declare
3 @IsPreprocessing bit = 1 declare @Message nvarchar(1000) declare @SortOrder int = 0 declare @DebugSwitch int = 0 declare @ModuleName varchar(3) declare
4 @ElementLast int declare @ElementBufferMulti table ( ElementIndex int identity primary key nonclustered , SQLCmdInsert nvarchar(max) collate database_default
5 , SQLCmdUpdate nvarchar(max) collate database_default , SQLCmdPre nvarchar(max) collate database_default ) declare @ElementCount int declare @ElementIndex
6 int SET XACT_ABORT OFF BEGIN TRY set nocount on select top 1 @ModuleName = left(t.UID_DialogTable, 3) from @StartElements x join DialogTable t  on x.Parameter1
7 like '<Key><T>' + t.TableName + '</T>%' if object_id(N'tempdb..#QBMCollectorReferenceElements') is null begin select @Message = '#LDS#Worktable #QBMCollectorReferenceElements missing|'
8 raiserror (@message, 18, 1) with nowait end truncate table #QBMCollectorReferenceElements insert into #QBMCollectorReferenceElements(TableName, Objectkey
9, XMarkedForDeletion, SortOrder, IsInsertRestrict, IsDeleteRestrict) select t.TableName, e.Parameter1, null , 0,0,0 from @StartElements e join DialogTable
10 t on e.Parameter1 like '<Key><T>' + t.TableName + '</T>%' join DialogColumn c1 on t.UID_DialogTable = c1.UID_DialogTable and c1.ColumnName = 'XObjectKey'
11 join DialogColumn c2 on t.UID_DialogTable = c2.UID_DialogTable and c2.ColumnName = 'XMarkedForDeletion' while exists (select top 1 1 from #QBMCollectorReferenceElements
12 re where re.XMarkedForDeletion is null ) begin select top 1 @SQLCmdUpdate = '
13		update  #QBMCollectorReferenceElements 
14			set XMarkedForDeletion = isnull(s.XmarkedFordeletion, 0)
15			from #QBMCollectorReferenceElements re left outer join '
16 + e.TableName + ' s on re.ObjectKey = s.XObjectKey
17			where re.TableName = ''' + e.TableName + '''' from #QBMCollectorReferenceElements e where e.XmarkedFordeletion
18 is null if @DebugSwitch > 0 begin print @SQLCmdupdate end exec sp_executesql @SQLCmdUpdate end if @DebugSwitch > 0 begin select * from #QBMCollectorReferenceElements
19 order by SortOrder end select @Lauf = 1 while @Lauf > 0 begin select @Lauf = 0 if @TopDown = 1 begin delete  @ElementBufferMulti  insert into @ElementBufferMulti
20 (SQLCmdInsert, SQLCmdUpdate, SQLCmdPre) select distinct 'select ''' + rq.ChildTable + ''', y.XObjectKey, ' +  case when rq.XIsInEffectColumn > ' ' then
21 ' (y.XMarkedForDeletion | (x.XMarkedForDeletion * ( 1 ^ y.XIsInEffect)))  ' else 'y.XMarkedForDeletion ' end + ', %SortOrder%, ' + rq.IsInsertRestrict+
22 ', ' + rq.IsDeleteRestrict + '
23			from ' + rq.ParentTable + ' x join #QBMCollectorReferenceElements w on x.XObjectKey = w.Objectkey
24						join ' + rq.ChildTable
25 + ' y on x.' + rq.ParentColumn + ' = y.' + rq.ChildColumn + '
26			where Not exists (select top 1 1
27								from #QBMCollectorReferenceElements e
28								where e.Objectkey = y.XObjectKey
29							)
30		'
31 , 'update  #QBMCollectorReferenceElements
32			set SortOrder = %SortOrder%
33			, IsInsertRestrict = ' + rq.IsInsertRestrict+ '
34			, IsDeleteRestrict = '
35 + rq.IsDeleteRestrict + '
36			from ' + rq.childTable + ' x join #QBMCollectorReferenceElements w on x.XObjectKey = w.Objectkey
37						join ' + rq.parentTable
38 + ' y on x.' + rq.childColumn + ' = y.' + rq.parentColumn + '
39			where exists (select top 1 1
40								from #QBMCollectorReferenceElements e
41								where e.Objectkey = y.XObjectKey
42							)
43			and w.SortOrder = 0 -- was initial
44		'
45 , '
46		update  #QBMCollectorReferenceElements
47					set SortOrder =  e.SortOrder +1
48		--	select w.objectkey, w.SortOrder, e.objectkey, e.SortOrder
49					from '
50 + rq.childTable + ' x join #QBMCollectorReferenceElements w on x.XObjectKey = w.Objectkey
51								join ' + rq.parentTable + ' y on x.' + rq.childColumn
52 + ' = y.' + rq.parentColumn + '
53								join (select * 
54										from #QBMCollectorReferenceElements
55									) as  e on e.Objectkey = y.XObjectKey
56					where w.SortOrder <=  e.SortOrder
57		'
58 from ( select r.ChildTable, r.ParentTable, r.ParentColumn, r.ChildColumn, cxi.ColumnName as XIsInEffectColumn , case r.ChildRestriction when 'IR' then
59 '1' else '0' end as IsInsertRestrict , case r.ParentRestriction when 'DR' then '1' else '0' end as IsDeleteRestrict from QBM_VQBMRelation r join #QBMCollectorReferenceElements
60 w on r.ParentTable = w.TableName join DialogTable t on r.UID_DialogTableChild = t.UID_DialogTable and t.TableType in ('T', 'V', 'M') left outer join DialogColumn
61 cxi on cxi.UID_DialogTable = t.UID_DialogTable and cxi.ColumnName = 'XIsInEffect' where (LEFT(r.UID_DialogTableChild, 3) = @ModuleName or @SameModule 
62= 0 ) and (r.ParentRestriction <> 'DS' or @ExcludeDS = 0 ) and exists (Select top 1 1 from DialogColumn c1 where r.UID_DialogTableChild = c1.UID_DialogTable
63 and c1.ColumnName = 'XObjectKey' ) and exists (Select top 1 1 from DialogColumn c1 where r.UID_DialogTableChild = c1.UID_DialogTable and c1.ColumnName
64 = 'XMarkedForDeletion' ) ) as rq  select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY
65 end else  begin delete  @ElementBufferMulti  insert into @ElementBufferMulti (SQLCmdInsert, SQLCmdUpdate, SQLCmdPre) select distinct 'select distinct '''
66 + rq.ParentTable + ''', y.XObjectKey, y.XMarkedForDeletion, %SortOrder%, ' + rq.IsInsertRestrict+ ', ' + rq.IsDeleteRestrict + '
67			from ' + rq.ChildTable
68 + ' x join #QBMCollectorReferenceElements w on x.XObjectKey = w.Objectkey
69						join ' + rq.ParentTable + ' y on x.' + rq.ChildColumn + ' = y.' + rq.ParentColumn
70 + '
71			where Not exists (select top 1 1
72								from #QBMCollectorReferenceElements e
73								where e.Objectkey = y.XObjectKey
74							)
75		', 'update  #QBMCollectorReferenceElements
76			set SortOrder = %SortOrder%
77			, IsInsertRestrict = '
78 + rq.IsInsertRestrict+ '
79			, IsDeleteRestrict = ' + rq.IsDeleteRestrict + '
80			from ' + rq.ParentTable + ' x join #QBMCollectorReferenceElements w on x.XObjectKey = w.Objectkey