Back to OIM Explorer

dbo.QBM_FSQXDateSubItemUpdate

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function. References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 7.541 characters

Interpretation

  • Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • references source dbo.QBM_FCVBinaryToString source text reference
  • references source dbo.QBM_FCVStringToIndent source text reference
  • references source dbo.QBM_FGIBitPatternXMarkedForDel source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGIXOriginChanged_Effect source text reference
  • references source dbo.QBM_FSQTableJoin source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_ZXMarkedForDeletionPush source text reference

References

Referenced By

Complete Source

SQL295 lines
1CREATE FUNCTION dbo.QBM_FSQXDateSubItemUpdate(2  @ChildTable varchar(30),3  @operation varchar(16)4) RETURNS nvarchar(max5)6AS7BEGIN8  DECLARE @ProzedurnameAusschluss varchar(64) = 'QBM_ZXMarkedForDeletionPush'9  DECLARE @IstMitXOrigin BIT = 010  DECLARE @IstMitXIsInEffect BIT = 011  DECLARE @IstMitXMarkedForDeletion BIT = 012  DECLARE @StringPatternOutStanding varchar(16)13  DECLARE @StringPatternOutStandingInv varchar(16)14  DECLARE @ElementBuffer QBM_YCursorBuffer15  DECLARE @UID_TaskNormal varchar(38) = 'QBM-K-XDateSubItemUpdate'16  DECLARE @UID_Task_FU varchar(38) = 'QBM-K-XDateSubItemUpdateFU'17  SELECT TOP 1 @UID_TaskNormal = ta.UID_Task18  FROM DialogTable t19    WITH(readpast)20  JOIN QBMDBQueueTask ta21    WITH(readpast)22    ON ta.UID_Task =23  LEFT(t.UID_DialogTable,24  3) + '-K-XDateSubItemUpdate'25  WHERE26    t.TableName = @ChildTable27  SELECT TOP 1 @UID_Task_FU = ta.UID_Task28  FROM DialogTable t29    WITH(readpast)30  JOIN QBMDBQueueTask ta31    WITH(readpast)32    ON ta.UID_Task =33  LEFT(t.UID_DialogTable,34  3) + '-K-XDateSubItemUpdateFU'35  WHERE36    t.TableName = @ChildTable37  SELECT38    @StringPatternOutStanding = dbo.QBM_FCVBinaryToString(CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|',39    0)),40    0)41  SELECT42    @StringPatternOutStandingInv = dbo.QBM_FCVBinaryToString(CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|',43    1)),44    0)45  DECLARE @opSynonym varchar(16)46  SELECT @opSynonym = CASE @operation47  WHEN 'insert' THEN48  'inserted'49  WHEN 'delete' THEN50  'deleted'51  ELSE ''52  END53  DECLARE @Vorspann nvarchar(max) = '54			declare @DBQueueElements_@ChildCol@ QBM_YDBQueueRaw55		56			insert into @DBQueueElements_@ChildCol@ (object, subobject, genprocid)'57  DECLARE @Nachspann nvarchar(max) = '58				) as x 5960			exec QBM_PDBQueueInsert_Bulk @UID_TaskXDateSubItemUpdate , @DBQueueElements_@ChildCol@ 61	'62  DECLARE @Condition nvarchar(max) = ''63  DECLARE @AllStatements nvarchar(max) = ''64  IF EXISTS(65    SELECT TOP 1 166    FROM DialogColumn c67    WITH(readpast)68  JOIN DialogTable t69    WITH(readpast)70    ON c.UID_DialogTable = t.UID_DialogTable71  WHERE72    t.TableName = @ChildTable AND c.ColumnName = 'XOrigin')73  BEGIN74    SELECT @IstMitXOrigin = 175    IF EXISTS(76      SELECT TOP 1 177      FROM DialogColumn c78      WITH(readpast)79    JOIN DialogTable t80      WITH(readpast)81      ON c.UID_DialogTable = t.UID_DialogTable82    WHERE83      t.TableName = @ChildTable AND c.ColumnName = 'XIsInEffect')84    BEGIN85      SELECT @IstMitXIsInEffect = 186    END87  END88  IF EXISTS(89    SELECT TOP 1 190    FROM DialogColumn c91    WITH(readpast)92  JOIN DialogTable t93    WITH(readpast)94    ON c.UID_DialogTable = t.UID_DialogTable95  WHERE96    t.TableName = @ChildTable AND c.ColumnName = 'XMarkedForDeletion')97  BEGIN98    SELECT @IstMitXMarkedForDeletion = 199  END100  IF @IstMitXOrigin = 1101  BEGIN102    SELECT @Condition = CASE @operation103    WHEN 'insert' THEN104    'where i.XOrigin > 0'105    WHEN 'update' THEN106    'where (dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, i.XOrigin, 1, 1) = 1 ' + NCHAR(13) + NCHAR(10) + '  @25923@   ' + NCHAR(13) + NCHAR(10) +107    '    ) ' + NCHAR(13) + NCHAR(10) + '@xdc@'108    WHEN 'delete' THEN109    ''110    ELSE ''111    END112    IF @IstMitXIsInEffect = 1113    BEGIN114      SELECT @Condition = CASE @operation115      WHEN 'insert' THEN116      'where i.XIsInEffect > 0'117      WHEN 'update' THEN118      'where (dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, i.XOrigin, d.XIsInEffect, i.XIsInEffect) = 1' + NCHAR(13) + NCHAR(10) + '  @25923@   ' + NCHAR119      (13) + NCHAR(10) + '    ) ' + NCHAR(13) + NCHAR(10) + '@xdc@'120      WHEN 'delete' THEN121      ''122      ELSE ''123      END124      IF @operation = 'update'125      BEGIN126        SELECT127          @Vorspann = '128		if update(XOrigin)129		 or update(XIsInEffect)130		 or @xdi@131		 begin132				' + @Vorspann133        SELECT @Nachspann = @Nachspann + '134		 end135				'136      END137    END138    ELSE139    BEGIN140      IF @operation = 'update'141      BEGIN142        SELECT @Vorspann = '143		if update(XOrigin)144		 or @xdi@145		 begin146				' + @Vorspann147        SELECT @Nachspann = @Nachspann + '148		 end149				'150      END151    END152  END153  ELSE154  BEGIN155    SELECT @Condition = CASE @operation156    WHEN 'insert' THEN157    '-- where 1=1'158    WHEN 'update' THEN159    'where 1 = 0' +NCHAR(13) +NCHAR(10) + '@xdc@'160    WHEN 'delete' THEN161    '@xdd@'162    ELSE ''163    END164    IF @operation = 'update' AND @IstMitXMarkedForDeletion = 1165    BEGIN166      SELECT @Vorspann = '167		if @xdi@168		 begin169				' + @Vorspann170      SELECT @Nachspann = @Nachspann + '171		 end172				'173    END174  END175  IF @operation = 'update' AND @IstMitXMarkedForDeletion = 1176  BEGIN177    SELECT178      @Condition = replace(@Condition,179      '@xdc@',180      '181							or (d.XMarkedForDeletion & ' + @StringPatternOutStanding + ' > 0182								and i.XMarkedForDeletion & ' + @StringPatternOutStanding + ' = 0183								and @ActionUser <> '''184      + @ProzedurnameAusschluss + '''185								' + CASE @IstMitXOrigin186      WHEN 1 THEN187      ' and i.XOrigin > 0'188    ELSE ''189    END + '190							  )')191  END192  IF @IstMitXMarkedForDeletion = 1193  BEGIN194    SELECT195      @vorspann = replace(@vorspann,196      '@xdi@',197      dbo.QBM_FCVStringToIndent(' (update(XMarkedForDeletion)198				and @ActionUser <> ''' + @ProzedurnameAusschluss + '''199			)200	',201      3))202    SELECT203      @Condition = replace(replace(@condition, '@25923@', 'and d.XMarkedForDeletion & ' + @StringPatternOutStanding + ' = 0'),204      '@xdd@',205      'where i.XMarkedForDeletion & ' + @StringPatternOutStanding + ' = 0')206  END207  ELSE208  BEGIN209    SELECT210      @vorspann = replace(@vorspann,211      '@xdi@',212      dbo.QBM_FCVStringToIndent(' 1=0', 3))213    SELECT214      @Condition = replace(replace(@condition, '@25923@', ''),215      '@xdd@',216      '-- where 1=1')217  END218  INSERT INTO @ElementBuffer(ContentFull)219  SELECT220    replace(@Vorspann,221    '@ChildCol@',222    cc.ColumnName) + '223			select x.uid , ''' + tp.UID_DialogTable + ''' ,   @GenProcID224		    from ( 			225					select distinct p.' + PKp.ColumnName226    + ' as uid227			    ' + CASE @operation228    WHEN 'update' THEN229    '	from ' + tc.TableName + ' i join deleted d on ' + dbo.QBM_FSQTableJoin(tc.TableName,230    'd',231    'i') + '232										join ' + tp.TableName + ' p on i.' + cc.ColumnName + ' = p.' + cp.ColumnName + '233			'234  ELSE '	from ' + @opSynonym + ' /*' + tc.TableName + '*/  i join ' + tp.TableName + ' p on i.' + cc.ColumnName + ' = p.' + cp.ColumnName + '235			'236  END + '			' + @Condition + replace(@Nachspann,237  '@ChildCol@',238  cc.ColumnName)239  FROM QBMRelation r240    WITH(readpast)241  JOIN DialogColumn cc242    WITH(readpast)243    ON r.UID_ChildColumn = cc.UID_DialogColumn244  JOIN DialogTable tc245    WITH(readpast)246    ON cc.UID_DialogTable = tc.UID_DialogTable AND tc.TableType = 'T'247  JOIN DialogColumn cp248    WITH(readpast)249    ON r.UID_ParentColumn = cp.UID_DialogColumn250  JOIN DialogTable tp251    WITH(readpast)252    ON cp.UID_DialogTable = tp.UID_DialogTable AND tp.TableType = 'T'253  JOIN DialogColumn cpch254    WITH(readpast)255    ON tp.UID_DialogTable = cpch.UID_DialogTable AND cpch.ColumnName = 'XDateSubItem'256  JOIN DialogColumn PKp257    WITH(readpast)258    ON pkp.UID_DialogTable = tp.UID_DialogTable AND pkp.IsPKMember = 1 AND NOT EXISTS(259  SELECT TOP 1 1260  FROM DialogColumn PKcheck261    WITH(readpast)262  WHERE263    PKcheck.UID_DialogTable = pkp.UID_DialogTable AND pkcheck.IsPKMember = 1 AND PKcheck.ColumnName <> pkp.ColumnName)264  WHERE265    (tc.isMNTable = 1 OR tc.IsMAllTable = 1) AND tc.TableName = @ChildTable AND r.IsForUpdateXDateSubItem = 1 AND @Condition > ' '266  SELECT267    @AllStatements = string_agg(convert(nvarchar(max), bu.ContentFull),268    N '')269  FROM @ElementBuffer bu270  IF @allStatements > ' '271  BEGIN272    SELECT273      @allStatements = CONCAT('274		-----------------------------------275		-- Handling of XDateSubItem 	276		-----------------------------------277		if dbo.QBM_FGISessionContext (''Fullsync'') > '' ''278			select @UID_TaskXDateSubItemUpdate = '''279      ,280      @UID_Task_FU,281      '''282		else283			select @UID_TaskXDateSubItemUpdate = ''',284      @UID_TaskNormal,285      '''286		',287      @allStatements,288      '289		-----------------------------------290		-- / Handling of XDateSubItem 	291		-----------------------------------')292  END293  endLabel:294  RETURN(@allStatements)295END
Open raw exported source
SQL ยท Raw98 lines
1        create   function dbo.QBM_FSQXDateSubItemUpdate (@ChildTable varchar(30) , @operation varchar(16) ) returns nvarchar(max) as begin declare2 @ProzedurnameAusschluss varchar(64) = 'QBM_ZXMarkedForDeletionPush' declare @IstMitXOrigin bit = 0 declare @IstMitXIsInEffect bit = 0 declare @IstMitXMarkedForDeletion3 bit = 0 declare @StringPatternOutStanding varchar(16) declare @StringPatternOutStandingInv varchar(16)  declare @ElementBuffer QBM_YCursorBuffer    declare4 @UID_TaskNormal varchar(38) = 'QBM-K-XDateSubItemUpdate' declare @UID_Task_FU varchar(38) = 'QBM-K-XDateSubItemUpdateFU' select top 1 @UID_TaskNormal 5= ta.UID_Task from DialogTable t with (readpast) join QBMDBQueueTask ta with (readpast) on ta.UID_Task = left(t.UID_DialogTable, 3) + '-K-XDateSubItemUpdate'6 where t.TableName = @ChildTable select top 1 @UID_Task_FU = ta.UID_Task from DialogTable t with (readpast) join QBMDBQueueTask ta with (readpast) on ta.UID_Task7 = left(t.UID_DialogTable, 3) + '-K-XDateSubItemUpdateFU' where t.TableName = @ChildTable select @StringPatternOutStanding = dbo.QBM_FCVBinaryToString(8 CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|',0)), 0) select @StringPatternOutStandingInv = dbo.QBM_FCVBinaryToString( CONVERT9(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|',1)), 0) declare @opSynonym varchar(16) select @opSynonym = case @operation when 'insert'10 then 'inserted' when 'delete' then 'deleted' else '' end declare @Vorspann nvarchar(max) = '11			declare @DBQueueElements_@ChildCol@ QBM_YDBQueueRaw12		13			insert into @DBQueueElements_@ChildCol@ (object, subobject, genprocid)'14 declare @Nachspann nvarchar(max) = '15				) as x 1617			exec QBM_PDBQueueInsert_Bulk @UID_TaskXDateSubItemUpdate , @DBQueueElements_@ChildCol@ 18	' declare19 @Condition nvarchar(max) = ''  declare @AllStatements nvarchar(max) = ''  if exists (select top 1 1 from DialogColumn c with (readpast) join DialogTable20 t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where t.TableName = @ChildTable and c.ColumnName = 'XOrigin' ) begin select @IstMitXOrigin 21= 1 if exists (select top 1 1 from DialogColumn c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where t.TableName22 = @ChildTable and c.ColumnName = 'XIsInEffect' ) begin select @IstMitXIsInEffect = 1 end end if exists (select top 1 1 from DialogColumn c with (readpast23) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where t.TableName = @ChildTable and c.ColumnName = 'XMarkedForDeletion' )24 begin select @IstMitXMarkedForDeletion = 1 end if @IstMitXOrigin = 1 begin select @Condition = case @operation when 'insert' then 'where i.XOrigin > 0'25 when 'update' then 'where (dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, i.XOrigin, 1, 1) = 1 ' + NCHAR(13) + NCHAR(10) + '  @25923@   ' + NCHAR(13) + NCHAR26(10) + '    ) ' + NCHAR(13) + NCHAR(10) + '@xdc@' when 'delete' then '' else '' end if @IstMitXIsInEffect = 1 begin select @Condition = case @operation27 when 'insert' then 'where i.XIsInEffect > 0' when 'update' then 'where (dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, i.XOrigin, d.XIsInEffect, i.XIsInEffect) = 1'28 + NCHAR(13) + NCHAR(10) + '  @25923@   ' + NCHAR(13) + NCHAR(10) + '    ) ' + NCHAR(13) + NCHAR(10) + '@xdc@' when 'delete' then '' else '' end if @operation29 = 'update' begin select @Vorspann = '30		if update(XOrigin)31		 or update(XIsInEffect)32		 or @xdi@33		 begin34				' + @Vorspann select @Nachspann = @Nachspann35 + '36		 end37				' end end else begin  if @operation = 'update' begin select @Vorspann = '38		if update(XOrigin)39		 or @xdi@40		 begin41				' + @Vorspann42 select @Nachspann = @Nachspann + '43		 end44				' end end end else begin  select @Condition = case @operation when 'insert' then '-- where 1=1' when 'update'45 then 'where 1 = 0'+NCHAR(13) +NCHAR(10) + '@xdc@' when 'delete' then '@xdd@' else '' end if @operation = 'update' and @IstMitXMarkedForDeletion = 1 begin46 select @Vorspann = '47		if @xdi@48		 begin49				' + @Vorspann select @Nachspann = @Nachspann + '50		 end51				' end end if @operation = 'update' and @IstMitXMarkedForDeletion52 = 1 begin select @Condition = replace(@Condition, '@xdc@', '53							or (d.XMarkedForDeletion & ' + @StringPatternOutStanding + ' > 054								and i.XMarkedForDeletion & '55 + @StringPatternOutStanding + ' = 056								and @ActionUser <> ''' + @ProzedurnameAusschluss + '''57								' + case @IstMitXOrigin when 1 then ' and i.XOrigin > 0'58 else '' end + '59							  )' ) end if @IstMitXMarkedForDeletion = 1 begin select @vorspann = replace(@vorspann, '@xdi@', dbo.QBM_FCVStringToIndent( ' (update(XMarkedForDeletion)60				and @ActionUser <> '''61 + @ProzedurnameAusschluss + '''62			)63	', 3)) select @Condition = replace(replace(@condition , '@25923@', 'and d.XMarkedForDeletion & ' + @StringPatternOutStanding64 + ' = 0') , '@xdd@', 'where i.XMarkedForDeletion & ' + @StringPatternOutStanding + ' = 0') end else begin select @vorspann = replace(@vorspann, '@xdi@'65, dbo.QBM_FCVStringToIndent( ' 1=0' , 3)) select @Condition = replace(replace(@condition , '@25923@', '') , '@xdd@', '-- where 1=1') end insert into @ElementBuffer66 (ContentFull) select  replace(@Vorspann, '@ChildCol@', cc.ColumnName) + '67			select x.uid , ''' + tp.UID_DialogTable + ''' ,   @GenProcID68		    from ( 			69					select distinct p.'70 + PKp.ColumnName + ' as uid71			    ' + case @operation when 'update' then '	from ' + tc.TableName + ' i join deleted d on ' + dbo.QBM_FSQTableJoin(tc.TableName72, 'd', 'i') + '73										join ' + tp.TableName + ' p on i.' + cc.ColumnName + ' = p.' + cp.ColumnName + '74			' else '	from ' + @opSynonym + ' /*' + 75tc.TableName + '*/  i join ' + tp.TableName + ' p on i.' + cc.ColumnName + ' = p.' + cp.ColumnName + '76			' end + '			' + @Condition + replace(@Nachspann77, '@ChildCol@', cc.ColumnName) from QBMRelation r with (readpast) join DialogColumn cc with (readpast) on r.UID_ChildColumn = cc.UID_DialogColumn join 78DialogTable tc with (readpast) on cc.UID_DialogTable = tc.UID_DialogTable and tc.TableType = 'T' join DialogColumn cp with (readpast) on r.UID_ParentColumn79 = cp.UID_DialogColumn join DialogTable tp with (readpast) on cp.UID_DialogTable = tp.UID_DialogTable and tp.TableType = 'T' join DialogColumn cpch with80 (readpast) on tp.UID_DialogTable = cpch.UID_DialogTable and cpch.ColumnName = 'XDateSubItem' join DialogColumn PKp with (readpast) on pkp.UID_DialogTable81 = tp.UID_DialogTable and pkp.IsPKMember = 1  and not exists (Select top 1 1 from DialogColumn PKcheck with (readpast) where PKcheck.UID_DialogTable = 82pkp.UID_DialogTable and pkcheck.IsPKMember = 1 and PKcheck.ColumnName <> pkp.ColumnName ) where ( tc.isMNTable = 1  or tc.IsMAllTable = 1 ) and tc.TableName83 = @ChildTable and r.IsForUpdateXDateSubItem = 1 and @Condition > ' ' select @AllStatements = string_agg(convert(nvarchar(max), bu.ContentFull )  , N''84 )  from @ElementBuffer bu if @allStatements > ' ' begin select @allStatements = concat('85		-----------------------------------86		-- Handling of XDateSubItem 	87		-----------------------------------88		if dbo.QBM_FGISessionContext (''Fullsync'') > '' ''89			select @UID_TaskXDateSubItemUpdate = '''90, @UID_Task_FU , '''91		else92			select @UID_TaskXDateSubItemUpdate = ''', @UID_TaskNormal , '''93		' , @allStatements , '94		-----------------------------------95		-- / Handling of XDateSubItem 	96		-----------------------------------'97 )  end endLabel: return(@allStatements) end 98