dbo.QBM_PSplittedElementProcess

1        create   procedure QBM_PSplittedElementProcess (@Sources QBM_YParameterList readonly , @SplittedElementType varchar(16) ) as begin declare
2 @IstInsert int = 0 declare @IstDelete int = 0 declare @IstUpdate int = 0 declare @DebugSwitch int = 0 declare @soll QBM_YSplittedElementHelper declare
3 @ist QBM_YSplittedElementHelper if @DebugSwitch > 0 begin print 'gelieferte @Sources für ElementType ' + @SplittedElementType select * from @Sources  
4   end if not exists (select top 1 1 from @Sources ) begin if @DebugSwitch > 0 begin print 'nix geliefert, return' end return end insert into @soll(UID_QBMSplittedLookup
5, UID_DialogColumn, ObjectKeyOwner, SplittedElement, UID_Person) select     dbo.QBM_FCVStringToGUID('', concat(x.UID_DialogColumn , x.ObjectKeyOwner , 
6x.SplittedElement ))    , x.UID_DialogColumn, x.ObjectKeyOwner, isnull(x.SplittedElement, ''), x.UID_Person from ( select s.Parameter2 as UID_DialogColumn
7 , s.Parameter3 as ObjectKeyOwner, s.ContentShort as SplittedElement , s.Parameter1 as UID_Person from @Sources s where s.HasContentFull = 0  union  select
8 s.Parameter2 as UID_DialogColumn, s.Parameter3 as ObjectKeyOwner, p.SplittedElement as SplittedElement, s.Parameter1 as UID_Person from @Sources s outer
9 apply (select case @SplittedElementType when 'Email' then replace(y.SplittedElement, 'smtp:', '') else y.SplittedElement end as SplittedElement from (
10 select trim(x.value) as SplittedElement from string_split (s.ContentFull, nchar(7)) x      ) as y ) as p where s.HasContentFull = 1  ) as x if @DebugSwitch
11 > 0 begin print 'Erstbefüllung soll' select * from @soll end insert into @ist(UID_QBMSplittedLookup, UID_DialogColumn, ObjectKeyOwner, SplittedElement
12, UID_Person ) select distinct t.UID_QBMSplittedLookup, t.UID_DialogColumn, t.ObjectKeyOwner, '', t.UID_Person  from QBMSplittedLookup t join @soll s on
13 t.ObjectKeyOwner = s.ObjectKeyOwner and t.UID_DialogColumn = s.UID_DialogColumn if @DebugSwitch > 0 begin print 'Erstbefüllung ist' select * from @ist
14 end update @soll set IsToInsert = 1 from @soll s left outer join @ist i on s.UID_QBMSplittedLookup = i.UID_QBMSplittedLookup where i.UID_QBMSplittedLookup
15 is null select @IstInsert = @@ROWCOUNT if @DebugSwitch > 0 begin print 'Soll nach Bewertung Neuaufnahme' select * from @soll end delete  @soll from @soll
16 s where s.SplittedElement = ''  if @DebugSwitch > 0 begin print 'Soll nach Löschung leere' select * from @soll end update @ist set isToDelete = 1 from
17 @ist i left outer join @soll s on i.UID_QBMSplittedLookup = s.UID_QBMSplittedLookup where s.UID_QBMSplittedLookup is null select @IstDelete = @@ROWCOUNT
18 update @soll set IsToUpdate = 1 from @ist i join @soll s on i.UID_QBMSplittedLookup = s.UID_QBMSplittedLookup where isnull(i.UID_Person, '') <> isnull
19(s.UID_Person, '') select @IstUpdate = @@ROWCOUNT if @DebugSwitch > 0 begin print 'Nach Bewertung soll' select * from @soll print 'Nach Bewertung ist' 
20select * from @ist end if @IstDelete > 0 begin delete QBMSplittedLookup from QBMSplittedLookup d join @ist s on d.UID_QBMSplittedLookup = s.UID_QBMSplittedLookup
21 where s.isToDelete = 1 end if @IstInsert > 0 begin insert into QBMSplittedLookup( UID_QBMSplittedLookup , UID_DialogColumn , ObjectKeyOwner , SplittedElement
22 , UID_Person , SplittedElementType  ) select s.UID_QBMSplittedLookup , s.UID_DialogColumn , s.ObjectKeyOwner , s.SplittedElement , s.UID_Person , @SplittedElementType
23  from @soll s where s.IsToInsert = 1 end if @IstUpdate > 0 begin update QBMSplittedLookup set UID_Person = s.UID_Person from QBMSplittedLookup i join 
24@soll s on i.UID_QBMSplittedLookup = s.UID_QBMSplittedLookup where s.IsToUpdate = 1 end endLabel: return end 
25