Back to OIM Explorer

dbo.QBM_FSQXMarkedForDeletionChg

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function. References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 4.914 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_FCVGUIDToModuleOwner source text reference
  • references source dbo.QBM_FGIBitPatternXMarkedForDel source text reference
  • references source dbo.QBM_FGIColumnExists source text reference
  • references source dbo.QBM_FGIColumnExistsInSchema source text reference
  • references source dbo.QBM_FSQTableJoin source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference

References

Referenced By

Complete Source

SQL184 lines
1CREATE FUNCTION dbo.QBM_FSQXMarkedForDeletionChg(2  @ParentTable varchar(30),3  @operation varchar(16)4) RETURNS nvarchar(max5)6AS7BEGIN8  DECLARE @Pattern varchar(64) = dbo.QBM_FCVBinaryToString(CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|OutStanding|',9  0)),10  0),11  @Pattern_nurO varchar(64) = dbo.QBM_FCVBinaryToString(CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|',12  0)),13  0),14  @Pattern_nurO_inv varchar(64) = dbo.QBM_FCVBinaryToString(CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|',15  1)),16  0)17  DECLARE @UID_DialogTable varchar(38)18  DECLARE @PKColumnName varchar(30)19  DECLARE @CountPK int20  DECLARE @ElementBuffer QBM_YCursorBuffer21  DECLARE @TSTables QBM_YParameterList22  DECLARE @erg nvarchar(max) = ''23  DECLARE @UID_TaskNormal varchar(38) = 'QBM-K-XMarkedForDeletionPush'24  SELECT25    TOP 1 @UID_DialogTable = t.UID_DialogTable,26    @PKColumnName = t.PKName1,27    @CountPK = CASE28    WHEN t.PKName2 > ' ' THEN29    230  ELSE 131  END32  FROM DialogTable t33    WITH(readpast)34  WHERE35    t.TableName = @ParentTable36  SELECT TOP 1 @UID_TaskNormal = ta.UID_Task37  FROM DialogTable t38    WITH(readpast)39  JOIN QBMDBQueueTask ta40    WITH(readpast)41    ON ta.UID_Task =42  LEFT(t.UID_DialogTable,43  3) + '-K-XMarkedForDeletionPush'44  WHERE45    t.TableName = @ParentTable46  IF @operation IN('Insert',47  'Delete')48  BEGIN49    GOTO EndLabel50  END51  IF dbo.QBM_FGIColumnExists(@ParentTable,52  'XMarkedForDeletion') = 153  BEGIN54    IF dbo.QBM_FGIColumnExists(@ParentTable,55    'XDateSubItem') = 156    BEGIN57      SELECT58        @erg = CONCAT('59			declare @DBQueueElements_XMarkedForDel QBM_YDBQueueRaw60	61			insert into @DBQueueElements_XMarkedForDel (object, subobject, genprocid)62			select x.uid, '''63        ,64        @UID_DialogTable,65        ''',   @GenProcID66			from ( 			67					select i.',68        @PKColumnName,69        ' as uid70	   				from ',71        @ParentTable,72        ' i join deleted d on ',73        dbo.QBM_FSQTableJoin(@ParentTable, 'd', 'i'),74        '75						where (d.XMarkedForDeletion & ',76        @Pattern,77        ')78						  <>  (i.XMarkedForDeletion & ',79        @Pattern,80        ')81				) as x 8283			exec QBM_PDBQueueInsert_Bulk ''',84        @UID_TaskNormal,85        ''' , @DBQueueElements_XMarkedForDel 86		')87    END88    IF EXISTS(89      SELECT TOP 1 190      FROM sys.tables v91      WITH(readpast)92    WHERE93      v.name = 'DPRNameSpaceHasDialogTable') AND EXISTS(94    SELECT TOP 1 195    FROM QBM_VQBMRelationALL a96    JOIN DialogTable tc97      WITH(readpast)98      ON a.UID_DialogTableChild = tc.UID_DialogTable99    JOIN DialogColumn cc100      WITH(readpast)101      ON tc.UID_DialogTable = cc.UID_DialogTable AND cc.ColumnName = 'XMarkedForDeletion'102    WHERE103      a.ParentTable = @ParentTable AND a.IsForUpdateXDateSubItem = 0 AND(tc.IsMAllTable = 1 OR tc.isMNTable = 1))104    BEGIN105      INSERT INTO @TSTables(Parameter1)106      SELECT107        DISTINCT t.TableName108      FROM DPRNameSpaceHasDialogTable nht109        WITH(readpast)110      JOIN DialogTable t111        WITH(readpast)112        ON nht.UID_DialogTable = t.UID_DialogTable113      INSERT INTO @ElementBuffer(Ident1,114      Ident2,115      Ident3)116      SELECT117        a.ParentColumn,118        a.ChildTable,119        a.ChildColumn120      FROM QBM_VQBMRelationALL a121      JOIN DialogTable tc122        WITH(readpast)123        ON a.UID_DialogTableChild = tc.UID_DialogTable124      JOIN DialogColumn cc125        WITH(readpast)126        ON tc.UID_DialogTable = cc.UID_DialogTable AND cc.ColumnName = 'XMarkedForDeletion'127      JOIN @TSTables etp128        ON etp.parameter1 = @ParentTable129      JOIN @TSTables etc130        ON etc.Parameter1 = a.ChildTable131      WHERE132        a.ParentTable = @ParentTable AND a.IsForUpdateXDateSubItem = 0 AND(tc.IsMAllTable = 1 OR tc.isMNTable = 1) AND dbo.QBM_FCVGUIDToModuleOwner133      (a.UID_DialogTableParent) = dbo.QBM_FCVGUIDToModuleOwner(a.UID_DialogTableChild) AND(EXISTS(134      SELECT TOP 1 1135      FROM QBMModuleDependCollection co136        WITH(readpast)137      WHERE138      LEFT(co.UID_ModulePredecessor, 3) = 'TSB' AND139      LEFT(co.UID_ModuleFollower, 3) =140      LEFT(dbo.QBM_FCVGUIDToModuleOwner(a.UID_DialogTableChild), 3)) OR dbo.QBM_FCVGUIDToModuleOwner(a.UID_DialogTableChild) = 'UCI') AND tc.UsageType141      = 'UserData'142      IF @@ROWCOUNT > 0143      BEGIN144        SELECT145          @erg = @erg + string_agg(convert(nvarchar(max), CONCAT('146				update ', bu.Ident2, '147						set XMarkedForDeletion = (e.XMarkedForDeletion & '148          , @Pattern_nurO_inv, ')149												 | (l.XMarkedForDeletion  & ', @Pattern_nurO, ')150												 ', CASE151          WHEN dbo.QBM_FGIColumnExistsInSchema(bu.Ident2, 'XDateUpdated') = 1 THEN152          ' , XDateUpdated = @XDate, XUserUpdated = @XUser'153        ELSE ''154        END, '155					from ', bu.Ident2, ' e join deleted d on e.', bu.Ident3, ' = d.', bu.Ident1, '156											join ' + @ParentTable + ' l on d.XObjectKey = l.XObjectKey157						where (d.XMarkedForDeletion ^ l.XMarkedForDeletion ) & '158        + @Pattern_nurO + ' > 0159						')),160        '')161        FROM @ElementBuffer bu162      END163    END164    IF @erg > ' '165    BEGIN166      SELECT167        @erg = '	168	if update(XMarkedForDeletion)169		begin170		if exists (select top 1 1171				from deleted d join ' + @ParentTable + ' l on d.XObjectKey = l.XObjectKey172				where (d.XMarkedForDeletion ^ l.XMarkedForDeletion ) & '173        + @Pattern + ' > 0174				)175		begin176' + @erg + '177178			end179		end'180    END181  END182  endLabel:183  RETURN(@erg)184END
Open raw exported source
SQL ยท Raw63 lines
1       create   function dbo.QBM_FSQXMarkedForDeletionChg (@ParentTable varchar(30) , @operation varchar(16) ) returns nvarchar(max) as begin declare2 @Pattern varchar(64) = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|OutStanding|', 0)), 0) , @Pattern_nurO3 varchar(64) = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|', 0)), 0) , @Pattern_nurO_inv varchar(644) = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|', 1)), 0) declare @UID_DialogTable varchar(38) declare5 @PKColumnName varchar(30) declare @CountPK int declare @ElementBuffer QBM_YCursorBuffer declare @TSTables QBM_YParameterList  declare @erg nvarchar(max6) = ''  declare @UID_TaskNormal varchar(38) = 'QBM-K-XMarkedForDeletionPush' select top 1 @UID_DialogTable = t.UID_DialogTable , @PKColumnName = t.PKName17 , @CountPK = case when t.PKName2 > ' ' then 2 else 1 end from DialogTable t with (readpast) where t.TableName = @ParentTable select top 1 @UID_TaskNormal8 = ta.UID_Task from DialogTable t with (readpast) join QBMDBQueueTask ta with (readpast) on ta.UID_Task = left(t.UID_DialogTable, 3) + '-K-XMarkedForDeletionPush'9 where t.TableName = @ParentTable if @operation in ('Insert', 'Delete') begin goto EndLabel end if dbo.QBM_FGIColumnExists(@ParentTable, 'XMarkedForDeletion'10) = 1 begin if dbo.QBM_FGIColumnExists(@ParentTable, 'XDateSubItem') = 1 begin select @erg = concat('11			declare @DBQueueElements_XMarkedForDel QBM_YDBQueueRaw12	13			insert into @DBQueueElements_XMarkedForDel (object, subobject, genprocid)14			select x.uid, '''15 , @UID_DialogTable , ''',   @GenProcID16			from ( 			17					select i.' , @PKColumnName , ' as uid18	   				from ' , @ParentTable , ' i join deleted d on '19 , dbo.QBM_FSQTableJoin(@ParentTable, 'd', 'i') , '20						where (d.XMarkedForDeletion & ' , @Pattern , ')21						  <>  (i.XMarkedForDeletion & ' , @Pattern22 , ')23				) as x 2425			exec QBM_PDBQueueInsert_Bulk ''', @UID_TaskNormal , ''' , @DBQueueElements_XMarkedForDel 26		' )  end     if exists (select top27 1 1 from sys.tables v with (readpast) where v.name = 'DPRNameSpaceHasDialogTable' )  and exists (select top 1 1 from QBM_VQBMRelationALL a join DialogTable28 tc with (readpast) on a.UID_DialogTableChild = tc.UID_DialogTable join DialogColumn cc with (readpast) on tc.UID_DialogTable = cc.UID_DialogTable and 29cc.ColumnName = 'XMarkedForDeletion' where a.ParentTable = @ParentTable and a.IsForUpdateXDateSubItem = 0 and ( tc.IsMAllTable = 1  or tc.isMNTable = 130 ) ) begin insert into @TSTables(Parameter1) select distinct t.TableName from DPRNameSpaceHasDialogTable nht with (readpast) join DialogTable t with (readpast31) on nht.UID_DialogTable = t.UID_DialogTable insert into @ElementBuffer (Ident1, Ident2, Ident3) select a.ParentColumn, a.ChildTable, a.ChildColumn from32 QBM_VQBMRelationALL a join DialogTable tc with (readpast) on a.UID_DialogTableChild = tc.UID_DialogTable join DialogColumn cc with (readpast) on tc.UID_DialogTable33 = cc.UID_DialogTable and cc.ColumnName = 'XMarkedForDeletion' join @TSTables etp on etp.parameter1 = @ParentTable join @TSTables etc on etc.Parameter134 = a.ChildTable where a.ParentTable = @ParentTable and a.IsForUpdateXDateSubItem = 0 and ( tc.IsMAllTable = 1  or tc.isMNTable = 1 ) and dbo.QBM_FCVGUIDToModuleOwner35(a.UID_DialogTableParent) = dbo.QBM_FCVGUIDToModuleOwner(a.UID_DialogTableChild)  and (exists (select top 1 1 from QBMModuleDependCollection co with (readpast36) where left(co.UID_ModulePredecessor, 3) = 'TSB' and left(co.UID_ModuleFollower, 3) = left(dbo.QBM_FCVGUIDToModuleOwner(a.UID_DialogTableChild), 3) ) 37 or dbo.QBM_FCVGUIDToModuleOwner(a.UID_DialogTableChild) = 'UCI' ) and tc.UsageType = 'UserData' if @@ROWCOUNT > 0  begin select @erg = @erg + string_agg38( convert(nvarchar(max), concat('39				update ' , bu.Ident2  , '40						set XMarkedForDeletion = (e.XMarkedForDeletion & ' , @Pattern_nurO_inv , ')41												 | (l.XMarkedForDeletion  & '42 , @Pattern_nurO , ')43												 ', case when dbo.QBM_FGIColumnExistsInSchema(bu.Ident2 , 'XDateUpdated' ) = 1 then ' , XDateUpdated = @XDate, XUserUpdated = @XUser'44 else '' end, '45					from ' , bu.Ident2  , ' e join deleted d on e.' , bu.Ident3  , ' = d.' , bu.Ident1 , '46											join ' + @ParentTable + ' l on d.XObjectKey = l.XObjectKey47						where (d.XMarkedForDeletion ^ l.XMarkedForDeletion ) & '48 + @Pattern_nurO + ' > 049						' )  )  , '' )  from @ElementBuffer bu end end   if @erg > ' ' begin select @erg = '	50	if update(XMarkedForDeletion)51		begin52		if exists (select top 1 153				from deleted d join '54 + @ParentTable + ' l on d.XObjectKey = l.XObjectKey55				where (d.XMarkedForDeletion ^ l.XMarkedForDeletion ) & ' + @Pattern + ' > 056				)57		begin58'59 + @erg + '6061			end62		end' end  end  endLabel: return(@erg) end 63