dbo.QBM_ZSplittedLookupFill

1create   procedure QBM_ZSplittedLookupFill (@SlotNumberDummy int , @UID_DialogColumn varchar(38) , @Dummy varchar(38) , @GenProcIDDummy varchar(38
2) ) as begin    declare @TableName varchar(30) declare @IsMultiValued bit declare @ColumnName varchar(30) declare @PersonColumn varchar(32) declare @SQLCMD
3 nvarchar(max) declare @Kernel nvarchar(max) declare @SplittedElementType varchar(16) declare @JoinDefinition nvarchar(max) declare @SQLFinal nvarchar(max
4) declare @DebugSwitch int = 0 BEGIN TRY select top 1 @TableName = t.TableName , @IsMultiValued = c.IsMultiValued , @ColumnName = c.ColumnName , @SplittedElementType
5 = c.SplittedLookupSupport , @JoinDefinition = dbo.QBM_FSQJoinsForSplittedLookup(t.UID_DialogTable) from DialogColumn c with (readpast) join DialogTable
6 t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where c.UID_DialogColumn = @UID_DialogColumn  and c.SplittedLookupSupport > ' '  if @IsMultiValued
7 = 0 begin select @Kernel = concat('
8						(select  ''', @UID_DialogColumn, ''' as UID_DialogColumn  , s.XObjectKey as ObjectKeyOwner, convert(nvarchar(400), s.'
9, @ColumnName, ') as SplittedElement 
10								, ', case when @JoinDefinition > ' ' then 'p.UID_Person' else 'NULL' end , ' as UID_Person 
11							from '
12, @TableName, ' s ', case when @JoinDefinition > ' ' then @JoinDefinition else '' end , ' 
13							where s.', @ColumnName, ' > '' ''
14						) as ke
15						'
16 )  end else begin select @Kernel = concat('
17						(select  ''', @UID_DialogColumn, ''' as UID_DialogColumn, s.XObjectKey as ObjectKeyOwner, spl.SplittedElement as SplittedElement
18							, '
19, case when @JoinDefinition > ' ' then 'p.UID_Person' else 'NULL' end , ' as UID_Person 
20							from ', @TableName, ' s cross apply (select case 
21																	when '''
22, @SplittedElementType, ''' = ''Email'' then replace(y.SplittedElement, ''smtp:'', '''') 
23																	else y.SplittedElement
24																end as SplittedElement
25															from (
26																	select  trim(xs.value) as SplittedElement
27																	from string_split (s.'
28, @ColumnName, ', nchar(7)) xs 
29																	where trim(xs.value) > '' '' 
30																) as y
31														) as spl
32													', case when
33 @JoinDefinition > ' ' then @JoinDefinition else '' end , '
34							where s.', @ColumnName, ' > '' ''
35						) as ke
36	' )  end     select @SQLCMD = concat
37(' select distinct  z.*, ''', @SplittedElementType, '''  as SplittedElementType
38				from 
39				(select dbo.QBM_FCVStringToGUID('''', concat(ke.UID_DialogColumn , ke.ObjectKeyOwner , ke.SplittedElement )) as UID_QBMSplittedLookup
40							, ke.UID_DialogColumn 
41							, ke.ObjectKeyOwner 
42							, ke.SplittedElement 
43							, ke.UID_Person 
44				 from 
45				'
46 , @Kernel , '
47				) as z
48			where not exists (select top 1 1
49								from QBMSplittedLookup e
50								where e.UID_QBMSplittedLookup = z.UID_QBMSplittedLookup
51									and e.UID_Person = z.UID_Person
52							)
53							'
54 )   select @SQLFinal = concat( 'merge into QBMSplittedLookup as t
55	using (
56		-- Block bisher
57 ' , @SQLCMD , '
58		-- / Block bisher
59		) as s 
60	on s.UID_QBMSplittedLookup = t.UID_QBMSplittedLookup
61when matched 
62	and isnull(s.UID_Person, '''') <> isnull(t.UID_Person, '''')
63		then update set t.UID_Person = s.UID_Person
64when not matched by target 
65	then insert(UID_QBMSplittedLookup 
66							, UID_DialogColumn 
67							, ObjectKeyOwner 
68							, SplittedElement 
69							, UID_Person 
70							, SplittedElementType)
71		values (s.UID_QBMSplittedLookup 
72							, s.UID_DialogColumn 
73							, s.ObjectKeyOwner 
74							, s.SplittedElement 
75							, s.UID_Person 
76							, s.SplittedElementType
77				)
78--when not matched by source 
79--	then delete	-- dauert zu lange, löschen bleibt wie gehabt
80;