Back to OIM Explorer

dbo.QBM_FSQTriggerSplittedLookup

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 6.675 characters

Interpretation

Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FCVStringToList source text reference
references source dbo.QBM_FCVFKDollarToRelationMVP source text reference
references source dbo.QBM_FGIColumnExists source text reference
references source dbo.QBM_FGIPrimaryKeyName source text reference
references source dbo.QBM_FSQJoinsForSplittedLookup source text reference
references source dbo.QBM_FSQTableJoin source text reference
references source dbo.QBM_PSplittedElementProcess source text reference

References

Referenced By

dbo.QBM_PTriggerWatchCreate

Complete Source

1CREATE FUNCTION dbo.QBM_FSQTriggerSplittedLookup(2  @Tablename varchar(30),3  @Operation varchar(16)4) RETURNS nvarchar(max5)6AS7BEGIN8  DECLARE @erg nvarchar(max) = ''9  DECLARE @ElementBuffer QBM_YCursorBuffer10  DECLARE @ElementCount int11  DECLARE @SourceSynonym nvarchar(16)12  DECLARE @Body nvarchar(max) = ''13  DECLARE @Body_sub nvarchar(max) = ''14  INSERT INTO @ElementBuffer(UID1,15  Ident1,16  Bit1,17  Ident3,18  ContentFull,19  LongIdent2)20  SELECT21    c.UID_DialogColumn,22    c.ColumnName,23    c.IsMultiValued,24    c.SplittedLookupSupport,25    dbo.QBM_FSQJoinsForSplittedLookup(t.UID_DialogTable),26    isnull(ac.AdditionalColumn,27    '')28  FROM DialogTable t29    WITH(readpast)30  JOIN DialogColumn c31    WITH(readpast)32    ON t.UID_DialogTable = c.UID_DialogTable33  JOIN sys.tables st34    WITH(readpast)35    ON st.name = t.TableName36  OUTER apply(37  SELECT r.ChildColumn AS AdditionalColumn38  FROM dbo.QBM_FCVStringToList(dbo.QBM_FCVFKDollarToRelationMVP(t.TableName, t.SplittedLookupSupport), char(7),39  1, 0) sp40  JOIN QBM_VQBMRelation r41    ON sp.ParameterValue = r.UID_QBMRelation42  WHERE43    sp.OrderNumber = 1 AND r.ChildTable = @tablename AND t.SplittedLookupSupport LIKE 'FK(%') AS ac44  WHERE45    t.TableName = @Tablename AND c.SplittedLookupSupport > ' '46  SELECT @ElementCount = @@ROWCOUNT47  IF @ElementCount > 0 AND @Operation = 'insert'48  BEGIN49    SELECT50      @Body = string_agg(convert(nvarchar(max), CONCAT('51declare @SplittedElements', '_', bu.Ident1,52      ' QBM_YParameterList 5354insert into @SplittedElements', '_', bu.Ident1, ' (Parameter1, Parameter2, Parameter3, HasContentFull, ',55    CASE bu.Bit156      WHEN 1 THEN57    'ContentFull'58    ELSE 'ContentShort'59    END, ')60 select ', CASE61    WHEN bu.ContentFull > ' ' THEN62    'p.UID_Person'63    ELSE 'Null'64    END, ', ''', bu.UID1, ''', s.XObjectKey, ', str(bu.Bit1), ', s.', bu.Ident1, '65	from inserted s ', CASE66    WHEN bu.ContentFull > ' ' THEN67    bu.ContentFull68    ELSE ''69    END, '70	where s.', bu.Ident1, ' > '' ''7172exec QBM_PSplittedElementProcess @SplittedElements', '_', bu.Ident1,73    ', ''', bu.Ident3, '''74')),75    N '')76    FROM @ElementBuffer bu77  END78  IF @ElementCount > 0 AND @Operation = 'update'79  BEGIN80    SELECT @SourceSynonym = CASE @TableName81    WHEN 'Person' THEN82    'p'83    ELSE 'w0'84    END85    SELECT86      @Body = string_agg(convert(nvarchar(max), CONCAT('87if update (', bu.Ident1, ') ', CASE88      WHEN @TableName <> 'Person' AND dbo.QBM_FGIColumnExists(@TableName, 'UID_Person') = 1 THEN89      ' or update(UID_Person)'90      WHEN @Tablename <> 'Person' AND bu.LongIdent2 <> 'UID_Person' AND bu.LongIdent2 > ' ' THEN91      ' or update(' + bu.LongIdent2 + ')'92    ELSE ''93    END, '94 begin 95	declare @SplittedElements', '_', bu.Ident1, ' QBM_YParameterList 9697	insert into @SplittedElements',98    '_', bu.Ident1, ' (Parameter1, Parameter2, Parameter3, HasContentFull, ', CASE bu.Bit199    WHEN 1 THEN100    'ContentFull'101    ELSE 'ContentShort'102    END, ')103		select ', CASE104    WHEN bu.ContentFull > ' ' THEN105    'p.UID_Person'106    ELSE 'Null'107    END, ', ''', bu.UID1, ''', ', @SourceSynonym, '.XObjectKey, ', str(bu.Bit1), ', ', @SourceSynonym, '.',108    bu.Ident1, '109		from deleted s ', CASE110    WHEN bu.ContentFull > ' ' THEN111    bu.ContentFull112    ELSE 'join ' + @TableName + ' w0 on  ' + dbo.QBM_FSQTableJoin(@Tablename, 's', 'w0')113    END, '114		where isnull(s.', bu.Ident1, ', '''') <> isnull(', @SourceSynonym, '.', bu.Ident1, ', '''')115			',116    CASE117      WHEN @TableName <> 'Person' AND dbo.QBM_FGIColumnExists(@TableName, 'UID_Person') = 1 THEN118    ' or isnull(s.UID_Person, '''') <> isnull(' + @SourceSynonym + '.UID_Person, '''')'119      WHEN @Tablename <> 'Person' AND bu.LongIdent2 <> 'UID_Person' AND bu.LongIdent2 > ' ' THEN120    ' or isnull(s.' + bu.LongIdent2 + ', '''') <> isnull(' + @SourceSynonym + '.' + bu.LongIdent2 + ', '''')'121    ELSE ''122    END, '123	exec QBM_PSplittedElementProcess @SplittedElements', '_', bu.Ident1, ', ''', bu.Ident3, '''124125 end126127')),128    N '')129    FROM @ElementBuffer bu130  END131  IF @ElementCount > 0 AND @Operation = 'delete'132  BEGIN133    SELECT134      @Body = '135delete QBMSplittedLookup 136	from QBMSplittedLookup s join deleted d on s.ObjectKeyOwner = d.XObjectKey137'138  END139  IF @Operation = 'update'140  BEGIN141    SELECT @SourceSynonym = CASE @TableName142    WHEN 'Person' THEN143    'p'144    ELSE 'w0'145    END146    SELECT147      @Body_sub = string_agg(convert(nvarchar(max), CONCAT('148if update (UID_Person) 149 begin 150	declare @SplittedElements',151      '_', roh.TableToSelectFrom, '_', roh.ColumnToSelect, ' QBM_YParameterList 152153	insert into @SplittedElements',154      '_', roh.TableToSelectFrom, '_', roh.ColumnToSelect, ' (Parameter1, Parameter2, Parameter3, HasContentFull, ',155    CASE roh.IsMultiValued156      WHEN 1 THEN157    'ContentFull'158    ELSE 'ContentShort'159    END, ')160		select p.UID_Person	,  ''', roh.UID_DialogColumn, ''', ', @SourceSynonym, '.XObjectKey, ', str(roh.IsMultiValued),161    ', ', @SourceSynonym, '.', roh.ColumnToSelect, '162		from ', roh.TableToSelectFrom, ' s ', replace(roh.joins,163    CONCAT('left outer join ', @tablename), CONCAT(' join  (select t.', roh.PKNameOfTable, ', t.UID_Person164										from deleted d join ',165    @tablename, ' t on  d.', roh.PKNameOfTable, ' = t.', roh.PKNameOfTable, '166										where ISNULL(d.UID_Person, '''' ) <> ISNULL(t.UID_Person, '''' )167										) '168    )), '169170	exec QBM_PSplittedElementProcess @SplittedElements', '_', roh.TableToSelectFrom, '_', roh.ColumnToSelect,171    ', ''', roh.SplittedLookupSupport, '''172173 end174175')),176    N '')177    FROM(178    SELECT179      ti.*,180      dbo.QBM_FSQJoinsForSplittedLookup(ti.UID_DialogTable) AS joins,181      dbo.QBM_FGIPrimaryKeyName(@tablename, 1) AS PKNameOfTable182    FROM(183    SELECT184      t.TableName AS TableToSelectFrom, c.ColumnName AS ColumnToSelect, c.UID_DialogColumn, c.IsMultiValued,185      c.SplittedLookupSupport, t.SplittedLookupSupport AS TableSplittedLookupSupport, t.UID_DialogTable186    FROM DialogTable t187    JOIN DialogColumn c188      ON c.UID_DialogTable = t.UID_DialogTable189    WHERE190      t.SplittedLookupSupport LIKE 'FK(%' AND c.SplittedLookupSupport > ' ' AND dbo.QBM_FGIColumnExists(t.TableName,191    'XObjectKey') = 1) AS ti192    WHERE193      EXISTS(194    SELECT TOP 1 1195    FROM dbo.QBM_FCVStringToList(dbo.QBM_FCVFKDollarToRelationMVP(ti.TableToSelectFrom, ti.TableSplittedLookupSupport),196    char(7), 1, 0) sp197    JOIN QBM_VQBMRelation r198      ON sp.ParameterValue = r.UID_QBMRelation199    WHERE200      sp.OrderNumberReverse = 1 AND r.ChildTable = @tablename) AND dbo.QBM_FGIColumnExists(@TableName,201    'UID_Person') = 1) AS roh202  END203  IF @Body_sub > ' '204  BEGIN205    SELECT206      @Body = CONCAT(@Body,207      @Body_sub)208  END209  IF @Body > ' '210  BEGIN211    SELECT212      @erg = CONCAT('213------------------------------------------------------214--	QBMSplittedLookup support215------------------------------------------------------216'217      ,218      @Body,219      '220------------------------------------------------------221--	/ QBMSplittedLookup support222------------------------------------------------------223'224      )225  END226  endLabel:227  RETURN(@erg)228END

Open raw exported source

1          create   function dbo.QBM_FSQTriggerSplittedLookup (@Tablename varchar(30) , @Operation varchar(16) ) returns nvarchar(max) as begin declare2 @erg nvarchar(max) = '' declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @SourceSynonym nvarchar(16) declare @Body nvarchar(max3) = '' declare @Body_sub nvarchar(max) = '' insert into @ElementBuffer (UID1 , Ident1  , Bit1   , Ident3  , ContentFull  , LongIdent2  ) select c.UID_DialogColumn4 , c.ColumnName, c.IsMultiValued , c.SplittedLookupSupport , dbo.QBM_FSQJoinsForSplittedLookup(t.UID_DialogTable) , isnull(ac.AdditionalColumn, '') from5 DialogTable t with (readpast) join DialogColumn c with (readpast) on t.UID_DialogTable = c.UID_DialogTable  join sys.tables st with (readpast) on st.name6 = t.TableName  outer apply ( select r.ChildColumn as AdditionalColumn from dbo.QBM_FCVStringToList(dbo.QBM_FCVFKDollarToRelationMVP(t.TableName, t.SplittedLookupSupport7), char(7), 1, 0 ) sp join QBM_VQBMRelation r on sp.ParameterValue = r.UID_QBMRelation where sp.OrderNumber = 1 and r.ChildTable = @tablename and t.SplittedLookupSupport8 like 'FK(%' ) as ac  where t.TableName = @Tablename and c.SplittedLookupSupport > ' ' select @ElementCount = @@ROWCOUNT if @ElementCount > 0 and @Operation9 = 'insert' begin select @Body = string_agg ( convert(nvarchar(max) , concat( '10declare @SplittedElements','_', bu.Ident1,' QBM_YParameterList 1112insert into @SplittedElements'13,'_', bu.Ident1,' (Parameter1, Parameter2, Parameter3, HasContentFull, ', case bu.Bit1  when 1 then 'ContentFull' else 'ContentShort' end , ')14 select '15 , case when bu.ContentFull  > ' ' then 'p.UID_Person' else 'Null' end , ', ''', bu.UID1 , ''', s.XObjectKey, ', str(bu.Bit1 ) , ', s.', bu.Ident1 , '16	from inserted s '17, case when bu.ContentFull  > ' ' then bu.ContentFull  else '' end , '18	where s.', bu.Ident1 , ' > '' ''1920exec QBM_PSplittedElementProcess @SplittedElements'21,'_', bu.Ident1,', ''' , bu.Ident3  , '''22' ) )  , N'' )  from @ElementBuffer bu end if @ElementCount > 0 and @Operation = 'update' begin select @SourceSynonym23 = case @TableName when 'Person' then 'p' else 'w0' end select @Body = string_agg( convert(nvarchar(max) , concat('24if update (', bu.Ident1 , ') ', case25 when @TableName <> 'Person' and dbo.QBM_FGIColumnExists(@TableName, 'UID_Person') = 1 then ' or update(UID_Person)'  when @Tablename <> 'Person' and bu.LongIdent226 <> 'UID_Person' and bu.LongIdent2 > ' ' then ' or update('+ bu.LongIdent2 + ')'  else '' end , '27 begin 28	declare @SplittedElements','_', bu.Ident1,29' QBM_YParameterList 3031	insert into @SplittedElements','_', bu.Ident1,' (Parameter1, Parameter2, Parameter3, HasContentFull, ', case bu.Bit1  when 1 32then 'ContentFull' else 'ContentShort' end , ')33		select ' , case when bu.ContentFull  > ' ' then 'p.UID_Person' else 'Null' end , ', ''', bu.UID1 , ''', '34, @SourceSynonym ,'.XObjectKey, ', str(bu.Bit1 ) , ', ',@SourceSynonym,'.', bu.Ident1 , '35		from deleted s ', case when bu.ContentFull  > ' ' then bu.ContentFull36  else 'join ' + @TableName + ' w0 on  ' + dbo.QBM_FSQTableJoin(@Tablename , 's', 'w0') end , '37		where isnull(s.', bu.Ident1 , ', '''') <> isnull(', 38@SourceSynonym,'.', bu.Ident1 , ', '''')39			', case when @TableName <> 'Person' and dbo.QBM_FGIColumnExists(@TableName, 'UID_Person') = 1 then ' or isnull(s.UID_Person, '''') <> isnull('40 + @SourceSynonym + '.UID_Person, '''')'  when @Tablename <> 'Person' and bu.LongIdent2 <> 'UID_Person' and bu.LongIdent2 > ' ' then ' or isnull(s.'+ bu.LongIdent241 + ', '''') <> isnull(' + @SourceSynonym + '.'+ bu.LongIdent2 + ', '''')'  else '' end , '42	exec QBM_PSplittedElementProcess @SplittedElements','_', bu.Ident143,', ''' , bu.Ident3 , '''4445 end4647' ) )  , N'' )  from @ElementBuffer bu end if @ElementCount > 0 and @Operation = 'delete' begin select @Body = '48delete QBMSplittedLookup 49	from QBMSplittedLookup s join deleted d on s.ObjectKeyOwner = d.XObjectKey50'51 end   if @Operation = 'update' begin select @SourceSynonym = case @TableName when 'Person' then 'p' else 'w0' end select @Body_sub = string_agg( convert52(nvarchar(max) , concat('53if update (UID_Person) 54 begin 55	declare @SplittedElements','_', roh.TableToSelectFrom,'_', roh.ColumnToSelect, ' QBM_YParameterList 5657	insert into @SplittedElements'58,'_', roh.TableToSelectFrom,'_', roh.ColumnToSelect, ' (Parameter1, Parameter2, Parameter3, HasContentFull, ', case roh.IsMultiValued when 1 then 'ContentFull'59 else 'ContentShort' end , ')60		select p.UID_Person	,  ''', roh.UID_DialogColumn, ''', ', @SourceSynonym ,'.XObjectKey, ', str(roh.IsMultiValued ) , ', '61,@SourceSynonym,'.', roh.ColumnToSelect , '62		from ', roh.TableToSelectFrom ,' s ', replace(roh.joins , concat('left outer join ', @tablename) , concat63( ' join  (select t.', roh.PKNameOfTable ,', t.UID_Person64										from deleted d join ', @tablename, ' t on  d.', roh.PKNameOfTable ,' = t.', roh.PKNameOfTable65 ,'66										where ISNULL(d.UID_Person, '''' ) <> ISNULL(t.UID_Person, '''' )67										) ' ) ) , '6869	exec QBM_PSplittedElementProcess @SplittedElements'70,'_', roh.TableToSelectFrom,'_', roh.ColumnToSelect, ', ''' , roh.SplittedLookupSupport, '''7172 end7374' ) )  , N'' )  from ( select ti.*, dbo.QBM_FSQJoinsForSplittedLookup75(ti.UID_DialogTable) as joins, dbo.QBM_FGIPrimaryKeyName(@tablename , 1) as PKNameOfTable from (  select t.TableName as TableToSelectFrom, c.ColumnName76 as ColumnToSelect, c.UID_DialogColumn, c.IsMultiValued, c.SplittedLookupSupport , t.SplittedLookupSupport as TableSplittedLookupSupport, t.UID_DialogTable77 from DialogTable t join DialogColumn c on c.UID_DialogTable = t.UID_DialogTable where t.SplittedLookupSupport like 'FK(%' and c.SplittedLookupSupport 78> ' ' and dbo.QBM_FGIColumnExists(t.TableName, 'XObjectKey') = 1 ) as ti where exists (select top 1 1 from dbo.QBM_FCVStringToList(dbo.QBM_FCVFKDollarToRelationMVP79(ti.TableToSelectFrom, ti.TableSplittedLookupSupport), char(7), 1, 0 ) sp join QBM_VQBMRelation r on sp.ParameterValue = r.UID_QBMRelation where sp.OrderNumberReverse80 = 1 and r.ChildTable = @tablename ) and dbo.QBM_FGIColumnExists(@TableName, 'UID_Person') = 1 ) as roh end if @Body_sub > ' ' begin select @Body = concat81(@Body, @Body_sub) end  if @Body > ' ' begin select @erg = concat('82------------------------------------------------------83--	QBMSplittedLookup support84------------------------------------------------------85'86 , @Body , '87------------------------------------------------------88--	/ QBMSplittedLookup support89------------------------------------------------------90'91 )  end endLabel: return(@erg) end 92