Back to OIM Explorer

dbo.QBM_ZSplittedLookupFill

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.999 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVStringToGUID source text reference
  • references source dbo.QBM_FSQJoinsForSplittedLookup source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL178 lines
1CREATE PROCEDURE QBM_ZSplittedLookupFill(2  @SlotNumberDummy int,3  @UID_DialogColumn varchar(38),4  @Dummy varchar(38),5  @GenProcIDDummy varchar(38)6)7AS8BEGIN9  DECLARE @TableName varchar(30)10  DECLARE @IsMultiValued BIT11  DECLARE @ColumnName varchar(30)12  DECLARE @PersonColumn varchar(32)13  DECLARE @SQLCMD nvarchar(max)14  DECLARE @Kernel nvarchar(max)15  DECLARE @SplittedElementType varchar(16)16  DECLARE @JoinDefinition nvarchar(max)17  DECLARE @SQLFinal nvarchar(max)18  DECLARE @DebugSwitch int = 019  BEGIN TRY20    SELECT21      TOP 1 @TableName = t.TableName,22      @IsMultiValued = c.IsMultiValued,23      @ColumnName = c.ColumnName,24      @SplittedElementType = c.SplittedLookupSupport,25      @JoinDefinition = dbo.QBM_FSQJoinsForSplittedLookup(t.UID_DialogTable)26    FROM DialogColumn c27      WITH(readpast)28    JOIN DialogTable t29      WITH(readpast)30      ON c.UID_DialogTable = t.UID_DialogTable31    WHERE32      c.UID_DialogColumn = @UID_DialogColumn AND c.SplittedLookupSupport > ' '33    IF @IsMultiValued = 034    BEGIN35      SELECT36        @Kernel = CONCAT('37						(select  ''',38        @UID_DialogColumn,39        ''' as UID_DialogColumn  , s.XObjectKey as ObjectKeyOwner, convert(nvarchar(400), s.',40        @ColumnName,41        ') as SplittedElement 42								, ',43      CASE44        WHEN @JoinDefinition > ' ' THEN45      'p.UID_Person'46      ELSE 'NULL'47      END,48      ' as UID_Person 49							from ',50      @TableName,51      ' s ',52      CASE53        WHEN @JoinDefinition > ' ' THEN54      @JoinDefinition55      ELSE ''56      END,57      ' 58							where s.',59      @ColumnName,60      ' > '' ''61						) as ke62						')63    END64    ELSE65    BEGIN66      SELECT67        @Kernel = CONCAT('68						(select  ''',69        @UID_DialogColumn,70        ''' as UID_DialogColumn, s.XObjectKey as ObjectKeyOwner, spl.SplittedElement as SplittedElement71							, ',72      CASE73        WHEN @JoinDefinition > ' ' THEN74      'p.UID_Person'75      ELSE 'NULL'76      END,77      ' as UID_Person 78							from ',79      @TableName,80      ' s cross apply (select case 81																	when ''',82      @SplittedElementType,83      ''' = ''Email'' then replace(y.SplittedElement, ''smtp:'', '''') 84																	else y.SplittedElement85																end as SplittedElement86															from (87																	select  trim(xs.value) as SplittedElement88																	from string_split (s.'89      ,90      @ColumnName,91      ', nchar(7)) xs 92																	where trim(xs.value) > '' '' 93																) as y94														) as spl95													',96      CASE97        WHEN @JoinDefinition > ' ' THEN98      @JoinDefinition99      ELSE ''100      END,101      '102							where s.',103      @ColumnName,104      ' > '' ''105						) as ke106	')107    END108    SELECT109      @SQLCMD = CONCAT(' select distinct  z.*, ''',110      @SplittedElementType,111      '''  as SplittedElementType112				from 113				(select dbo.QBM_FCVStringToGUID('''', concat(ke.UID_DialogColumn , ke.ObjectKeyOwner , ke.SplittedElement )) as UID_QBMSplittedLookup114							, ke.UID_DialogColumn 115							, ke.ObjectKeyOwner 116							, ke.SplittedElement 117							, ke.UID_Person 118				 from 119				'120      ,121      @Kernel,122      '123				) as z124			where not exists (select top 1 1125								from QBMSplittedLookup e126								where e.UID_QBMSplittedLookup = z.UID_QBMSplittedLookup127									and e.UID_Person = z.UID_Person128							)129							'130      )131    SELECT132      @SQLFinal = CONCAT('merge into QBMSplittedLookup as t133	using (134		-- Block bisher135 ',136      @SQLCMD,137      '138		-- / Block bisher139		) as s 140	on s.UID_QBMSplittedLookup = t.UID_QBMSplittedLookup141when matched 142	and isnull(s.UID_Person, '''') <> isnull(t.UID_Person, '''')143		then update set t.UID_Person = s.UID_Person144when not matched by target 145	then insert(UID_QBMSplittedLookup 146							, UID_DialogColumn 147							, ObjectKeyOwner 148							, SplittedElement 149							, UID_Person 150							, SplittedElementType)151		values (s.UID_QBMSplittedLookup 152							, s.UID_DialogColumn 153							, s.ObjectKeyOwner 154							, s.SplittedElement 155							, s.UID_Person 156							, s.SplittedElementType157				)158--when not matched by source 159--	then delete	-- dauert zu lange, löschen bleibt wie gehabt160;161'162      )163    IF @DebugSwitch > 0164    BEGIN165      print @sqlfinal166    END167    EXEC sp_executesql @sqlfinal168  END TRY169  BEGIN CATCH170    EXEC QBM_PSessionErrorAdd DEFAULT171    RAISERROR('',172    18,173    1)174      WITH NOWAIT175  END CATCH176  ende:177  RETURN178END
Open raw exported source
SQL · Raw84 lines
1create   procedure QBM_ZSplittedLookupFill (@SlotNumberDummy int , @UID_DialogColumn varchar(38) , @Dummy varchar(38) , @GenProcIDDummy varchar(382) ) as begin    declare @TableName varchar(30) declare @IsMultiValued bit declare @ColumnName varchar(30) declare @PersonColumn varchar(32) declare @SQLCMD3 nvarchar(max) declare @Kernel nvarchar(max) declare @SplittedElementType varchar(16) declare @JoinDefinition nvarchar(max) declare @SQLFinal nvarchar(max4) declare @DebugSwitch int = 0 BEGIN TRY select top 1 @TableName = t.TableName , @IsMultiValued = c.IsMultiValued , @ColumnName = c.ColumnName , @SplittedElementType5 = c.SplittedLookupSupport , @JoinDefinition = dbo.QBM_FSQJoinsForSplittedLookup(t.UID_DialogTable) from DialogColumn c with (readpast) join DialogTable6 t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where c.UID_DialogColumn = @UID_DialogColumn  and c.SplittedLookupSupport > ' '  if @IsMultiValued7 = 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 ke15						'16 )  end else begin select @Kernel = concat('17						(select  ''', @UID_DialogColumn, ''' as UID_DialogColumn, s.XObjectKey as ObjectKeyOwner, spl.SplittedElement as SplittedElement18							, '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.SplittedElement24																end as SplittedElement25															from (26																	select  trim(xs.value) as SplittedElement27																	from string_split (s.'28, @ColumnName, ', nchar(7)) xs 29																	where trim(xs.value) > '' '' 30																) as y31														) as spl32													', case when33 @JoinDefinition > ' ' then @JoinDefinition else '' end , '34							where s.', @ColumnName, ' > '' ''35						) as ke36	' )  end     select @SQLCMD = concat37(' select distinct  z.*, ''', @SplittedElementType, '''  as SplittedElementType38				from 39				(select dbo.QBM_FCVStringToGUID('''', concat(ke.UID_DialogColumn , ke.ObjectKeyOwner , ke.SplittedElement )) as UID_QBMSplittedLookup40							, ke.UID_DialogColumn 41							, ke.ObjectKeyOwner 42							, ke.SplittedElement 43							, ke.UID_Person 44				 from 45				'46 , @Kernel , '47				) as z48			where not exists (select top 1 149								from QBMSplittedLookup e50								where e.UID_QBMSplittedLookup = z.UID_QBMSplittedLookup51									and e.UID_Person = z.UID_Person52							)53							'54 )   select @SQLFinal = concat( 'merge into QBMSplittedLookup as t55	using (56		-- Block bisher57 ' , @SQLCMD , '58		-- / Block bisher59		) as s 60	on s.UID_QBMSplittedLookup = t.UID_QBMSplittedLookup61when matched 62	and isnull(s.UID_Person, '''') <> isnull(t.UID_Person, '''')63		then update set t.UID_Person = s.UID_Person64when 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.SplittedElementType77				)78--when not matched by source 79--	then delete	-- dauert zu lange, löschen bleibt wie gehabt80;81'82 )  if @DebugSwitch > 0 begin print @sqlfinal end exec sp_executesql @sqlfinal END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18,83 1) WITH NOWAIT END CATCH  ende: return end 84