Back to OIM Explorer

dbo.DPR_FSQTriggerWatchMemberShip

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 18.473 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_FCVBinaryToString source text reference
  • references source dbo.QBM_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_FCVStringToIndent source text reference
  • references source dbo.QBM_FGIBitPatternXMarkedForDel source text reference
  • references source dbo.QBM_FGIColumnExists source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGITableName source text reference
  • references source dbo.QBM_FGIXOriginChanged_Effect source text reference
  • references source dbo.QBM_FSQTableJoin source text reference
  • references source dbo.QBM_PFESimulationFill source text reference

References

Referenced By

Complete Source

SQL597 lines
1CREATE FUNCTION dbo.DPR_FSQTriggerWatchMemberShip(2  @ChildTable varchar(30),3  @Operation varchar(16)4) RETURNS nvarchar(max5)6AS7BEGIN8  DECLARE @IsXOriginHandling BIT = 09  DECLARE @IsXIsInEffectHandling BIT = 010  DECLARE @IsXMarkedForDeletionHandling BIT = 011  DECLARE @Erg nvarchar(max) = ''12  DECLARE @ErgSim nvarchar(max) = ''13  DECLARE @ContainerTable varchar(30)14  DECLARE @ContainerColumn varchar(30)15  DECLARE @ChildConnectContainer varchar(30)16  DECLARE @MemberTable varchar(30)17  DECLARE @MemberColumn varchar(30)18  DECLARE @ChildConnectMember varchar(30)19  DECLARE @UID_ContainerTable varchar(38)20  DECLARE @UID_DPRNameSpace varchar(38)21  DECLARE @ContainerObjectKey nvarchar(1000)22  DECLARE @MemberObjectKey nvarchar(1000)23  DECLARE @OpAddDel nvarchar(max)24  DECLARE @OpSynonym varchar(16) = 'deleted'25  DECLARE @joinDeletedReal nvarchar(max) = '--'26  DECLARE @whereClauseSoFar nvarchar(max) = '1=1'27  DECLARE @whereClause nvarchar(max)28  DECLARE @StringPatternFullsync nvarchar(100)29  DECLARE @StringPatternOutStanding nvarchar(100)30  DECLARE @ElementNummer int = 031  DECLARE @DebugSwitch int = 032  DECLARE @DebugMessage nvarchar(max)33  DECLARE @DebugOnly nvarchar(16) = '--Debug '34  DECLARE @crlf nvarchar(20) = nchar(13) + nchar(10)35  DECLARE @AdditionalWhereClause nvarchar(max)36  DECLARE @ElementBuffer QBM_YCursorBuffer37  DECLARE @ElementCount int38  DECLARE @ElementIndex int39  DECLARE @Pattern1 nvarchar(max) = '40insert into DPRMemberShipAction	(41	 UID_DPRMemberShipAction42	, ObjectKeyMN  43	, Operation 44	, ObjectKeyBase 45	, ObjectKeyMember 46	, UID_DPRNameSpace47	, OperationDate48	, XObjectKey49			)50'51  DECLARE @Pattern2 nvarchar(max) = '52select 	 y.UID_DPRMemberShipAction53	, y.ObjectKeyMN  54	, y.Operation 55	, y.ObjectKeyBase 56	, y.ObjectKeyMember 57	, y.UID_DPRNameSpace58	, y.OperationDate59	, dbo.QBM_FCVElementToObjectKey1(''DPRMemberShipAction'', ''UID_DPRMemberShipAction'', y.UID_DPRMemberShipAction)60from (61	 select 62		i.XObjectKey as ObjectKeyMN63		, @OpAddDel as Operation  64		, @ContainerObjectKey as ObjectKeyBase65		, @MemberObjectKey as ObjectKeyMember66		, ''@UID_DPRNameSpace'' as UID_DPRNameSpace67		, @TrackingDate as OperationDate68		, newid() as UID_DPRMemberShipAction69		from @OPSynonym i -- @ChildTable70				@joinDeletedReal@71		join @Containertable ContainerTable on i.@ChildConnectContainer = ContainerTable.@ContainerColumn72						join @MemberTable MemberTable on i.@ChildConnectMember = MemberTable.@MemberColumn73						@v3336574		@WhereClause75	) as y76'77  DECLARE @pattern2_33365 nvarchar(max)78  DECLARE @Pattern3 nvarchar(max) = '79 80	if @IsSimulationMode = 181	 begin8283		delete @SimulationModeBuffer8485		insert into @SimulationModeBuffer (operation, BaseObjectType, ColumnName, Objectkey, OldValue) 86		select ''I'', ''DPRMemberShipAction'', ''UID_DPRMemberShipAction'', m.XObjectKey , '''' 87		from DPRMemberShipAction m88			where m.OperationDate = @TrackingDate89		union all90		select ''I'', ''DPRMemberShipAction'', ''ObjectKeyMN'', m.XObjectKey , '''' 91		from DPRMemberShipAction m92			where m.OperationDate = @TrackingDate93		union all94		select ''I'', ''DPRMemberShipAction'', ''Operation'', m.XObjectKey , '''' 95		from DPRMemberShipAction m96			where m.OperationDate = @TrackingDate97		union all98		select ''I'', ''DPRMemberShipAction'', ''ObjectKeyBase'', m.XObjectKey , '''' 99		from DPRMemberShipAction m100			where m.OperationDate = @TrackingDate101		union all102		select ''I'', ''DPRMemberShipAction'', ''ObjectKeyMember'', m.XObjectKey , '''' 103		from DPRMemberShipAction m104			where m.OperationDate = @TrackingDate105		union all106		select ''I'', ''DPRMemberShipAction'', ''UID_DPRNameSpace'', m.XObjectKey , '''' 107		from DPRMemberShipAction m108			where m.OperationDate = @TrackingDate109		union all110		select ''I'', ''DPRMemberShipAction'', ''OperationDate'', m.XObjectKey , '''' 111		from DPRMemberShipAction m112			where m.OperationDate = @TrackingDate113		union all114		select ''I'', ''DPRMemberShipAction'', ''ObjectKeyMN'', m.XObjectKey , '''' 115		from DPRMemberShipAction m116			where m.OperationDate = @TrackingDate117118		exec QBM_PFESimulationFill @SimulationModeBuffer119120	 end '121  SELECT122    @StringPatternOutStanding = dbo.QBM_FCVBinaryToString(CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|',123    0)),124    0)125  IF NOT EXISTS(126    SELECT TOP 1 1127    FROM DialogTable t128    WITH(readpast)129  WHERE130    t.TableName = @ChildTable AND(t.isMNTable = 1 OR t.IsMAllTable = 1))131  BEGIN132    IF @DebugSwitch > 0133    BEGIN134      SELECT @DebugMessage = @DebugOnly + ' keine m:n oder m:all ' + @ChildTable135      SELECT @Erg = @DebugMessage136    END137    GOTO endLabel138  END139  SELECT140    @IsXMarkedForDeletionHandling = dbo.QBM_FGIColumnExists(@ChildTable,141    'XMarkedForDeletion')142  SELECT143    @IsXOriginHandling = dbo.QBM_FGIColumnExists(@ChildTable,144    'XOrigin')145  IF @IsXOriginHandling = 1146  BEGIN147    SELECT148      @IsXIsInEffectHandling = dbo.QBM_FGIColumnExists(@ChildTable,149      'XIsInEffect')150  END151  IF @Operation = 'insert'152  BEGIN153    SELECT @OpSynonym = 'inserted'154    SELECT @OpAddDel = '''ADD'''155    IF @IsXOriginHandling = 1156    BEGIN157      SELECT @whereClauseSoFar = ' i.XOrigin > 0'158    END159    IF @IsXIsInEffectHandling = 1160    BEGIN161      SELECT @whereClauseSoFar = @whereClauseSoFar + ' and i.XIsInEffect = 1'162    END163  END164  IF @Operation = 'delete'165  BEGIN166    SELECT @OpAddDel = '''DEL'''167    IF @IsXOriginHandling = 1168    BEGIN169      IF @DebugSwitch > 0170      BEGIN171        SELECT @DebugMessage = @DebugOnly + ' dann passiert beim Delete nix mehr' + @ChildTable172        SELECT @Erg = @DebugMessage173      END174      GOTO Restposten441234175    END176  END177  IF @Operation = 'Update'178  BEGIN179    IF @IsXOriginHandling = 0 AND @IsXMarkedForDeletionHandling = 0180    BEGIN181      IF @DebugSwitch > 0182      BEGIN183        SELECT184          @DebugMessage = @DebugOnly + ' dann passiert alles im Delete, brauchen wir kein update ' + @ChildTable185        SELECT @Erg = @DebugMessage186      END187      GOTO endLabel188    END189    SELECT190      @whereClauseSoFar = @crlf + '				' + CASE @IsXOriginHandling * 100 + @IsXIsInEffectHandling *10 + @IsXMarkedForDeletionHandling191      WHEN 001 THEN192      ' i.XMarkedForDeletion & ' + @StringPatternOutStanding + ' > 0 and t.XMarkedForDeletion & ' + @StringPatternOutStanding + ' = 0 '193      WHEN 100 THEN194      'dbo.QBM_FGIXOriginChanged_Effect(i.XOrigin, t.XOrigin, 1, 1) = 1'195      WHEN 101 THEN196      '( i.XOrigin > 0 and t.XOrigin = 0 and t.XMarkedForDeletion & ' + @StringPatternOutStanding + ' = 0 and i.XMarkedForDeletion & ' + @StringPatternOutStanding197      + ' = 0 )198							or ( i.XOrigin = 0 and t.XOrigin > 0	)199							or (i.XMarkedForDeletion & ' + @StringPatternOutStanding + ' > 0 200								and t.XMarkedForDeletion & '201      + @StringPatternOutStanding + ' = 0 202								and t.XOrigin > 0203							)'204      WHEN 110 THEN205      'dbo.QBM_FGIXOriginChanged_Effect(i.XOrigin, t.XOrigin, i.XIsInEffect, t.XIsInEffect) = 1'206      WHEN 111 THEN207      CONCAT('( i.XIsInEffect > 0 and t.XIsInEffect = 0 and t.XMarkedForDeletion & ',208      @StringPatternOutStanding,209      ' = 0 and i.XMarkedForDeletion & ' + @StringPatternOutStanding + ' = 0 )210						or ( i.XOrigin > 0 and t.XOrigin = 0 and i.XIsInEffect > 0  and t.XMarkedForDeletion & '211      + @StringPatternOutStanding + ' = 0 and i.XMarkedForDeletion & ' + @StringPatternOutStanding + ' = 0 )212						 or ( i.XIsInEffect = 0 and t.XIsInEffect > 0 and t.XMarkedForDeletion & '213      ,214      @StringPatternOutStanding,215      ' = 0 	)216						 or (i.XMarkedForDeletion & ',217      @StringPatternOutStanding,218      ' > 0 219								and t.XMarkedForDeletion & ',220      @StringPatternOutStanding,221      ' = 0 222								and t.XIsInEffect > 0223								and t.XOrigin > 0224							)')225    ELSE ' 1 = 0'226    END227    SELECT228      @OpAddDel = '			' + CASE @IsXOriginHandling * 100 + @IsXIsInEffectHandling *10 + @IsXMarkedForDeletionHandling229      WHEN 001 THEN230      'case 231					when t.XMarkedForDeletion & ' + @StringPatternOutStanding + ' = 0 232						then ''ADD''233						else ''DEL''234				   end'235      WHEN 100 THEN236      'case 237					when (t.XOrigin > 0 and i.XOrigin = 0 )238						then ''ADD''239					else ''DEL''240				   end'241      WHEN 101 THEN242      'case 243					when (t.XOrigin > 0 and i.XOrigin = 0 )244						or ( i.XMarkedForDeletion & ' + @StringPatternOutStanding + ' > 0 245								and t.XMarkedForDeletion & '246      + @StringPatternOutStanding + ' = 0 247								and t.XOrigin > 0248							)249						then ''ADD''250					else ''DEL''251				   end'252      WHEN 110 THEN253      'case 254					when (t.XOrigin > 0 and t.XIsInEffect = 1 and i.XIsInEffect = 0 )255						 then ''ADD''256					else ''DEL''257				   end'258      WHEN 111 THEN259      CONCAT('case 260					when (t.XOrigin > 0 and t.XIsInEffect = 1 and i.XIsInEffect = 0 and t.XMarkedForDeletion & ',261      @StringPatternOutStanding,262      ' = 0 )263						 or ( i.XMarkedForDeletion & ',264      @StringPatternOutStanding,265      ' > 0 266								and t.XMarkedForDeletion & ',267      @StringPatternOutStanding,268      ' = 0 269								and t.XOrigin > 0270							)	271						 then ''ADD''272					else ''DEL''273				   end')274    ELSE '  ''ADD'' '275    END276    SELECT277      @joinDeletedReal = ' join ' + @ChildTable + ' t on ' + dbo.QBM_FSQTableJoin(@ChildTable,278      'i',279      't')280  END281  INSERT INTO @ElementBuffer(Ident1,282  Ident2,283  Ident3,284  UID2,285  UID3,286  UID4,287  UID1,288  ContentFull,289  ObjectKey1,290  ObjectKey2)291  SELECT292    r.ParentTable AS ContainerTable,293    r.ParentColumn AS ContainerColumn,294    r.ChildColumn AS ChildConnectContainer,295    rm.ParentTable AS MemberTable,296    rm.ParentColumn AS MemberColumn,297    rm.ChildColumn AS ChildConnectMember,298    nht.UID_DPRNameSpace,299    nht.WhereClause AS AdditionalWhereClause,300    'ContainerTable.XObjectKey' AS ContainerObjectKey,301    'MemberTable.XObjectKey' AS MemberObjectKey302  FROM QBM_VQBMRelation r303  JOIN DPRNameSpaceHasDialogTable nht304    WITH(readpast)305    ON r.UID_DialogTableChild = nht.UID_DialogTable AND r.IsForUpdateXDateSubItem = 1306  JOIN QBM_VQBMRelation rm307    ON r.UID_QBMRelationMN = rm.UID_QBMRelation308  JOIN DialogTable tc309    WITH(readpast)310    ON r.ChildTable = tc.TableName AND tc.isMNTable = 1311  WHERE312    r.ChildTable = @ChildTable AND r.IsForUpdateXDateSubItem = 1 AND nht.IsAdHocSingleMemberShip = 1313  UNION314  SELECT315    r.ParentTable AS ContainerTable,316    r.ParentColumn AS ContainerColumn,317    r.ChildColumn AS ChildConnectContainer,318    dbo.QBM_FGITableName(rm.UID_DialogTableReference) AS MemberTable,319    'XObjectKey' AS MemberColumn,320    ok.ColumnName AS ChildConnectMember,321    nht.UID_DPRNameSpace,322    nht.WhereClause AS AdditionalWhereClause,323    'ContainerTable.XObjectKey' AS ContainerObjectKey,324    'i.' + ok.ColumnName AS MemberObjectKey325  FROM QBM_VQBMRelation r326  JOIN DPRNameSpaceHasDialogTable nht327    WITH(readpast)328    ON r.UID_DialogTableChild = nht.UID_DialogTable AND r.IsForUpdateXDateSubItem = 1329  JOIN DialogColumn ok330    WITH(readpast)331    ON ok.UID_DialogTable = r.UID_DialogTableChild332  JOIN DialogValidDynamicRef rm333    WITH(readpast)334    ON ok.UID_DialogColumn = rm.UID_DialogColumn335  JOIN DialogTable tc336    WITH(readpast)337    ON ok.UID_DialogTable = tc.UID_DialogTable AND tc.IsMAllTable = 1338  WHERE339    r.ChildTable = @ChildTable AND r.IsForUpdateXDateSubItem = 1 AND nht.IsAdHocSingleMemberShip = 1340  SELECT @ElementCount = @@ROWCOUNT341  SELECT @ElementIndex = 1342  WHILE @ElementIndex <= @ElementCount343  BEGIN344    SELECT345      TOP 1 @ContainerTable = bu.Ident1,346      @ContainerColumn = bu.Ident2,347      @ChildConnectContainer = bu.Ident3,348      @MemberTable = bu.UID2,349      @MemberColumn = bu.UID3,350      @ChildConnectMember = bu.UID4,351      @UID_DPRNameSpace = bu.UID1,352      @AdditionalWhereClause = bu.ContentFull,353      @ContainerObjectKey = bu.ObjectKey1,354      @MemberObjectKey = bu.ObjectKey2355    FROM @ElementBuffer bu356    WHERE357      bu.ElementIndex = @ElementIndex358    IF @AdditionalWhereClause > ' '359    BEGIN360      SELECT361        @whereClause = '362			where ( ' + @AdditionalWhereClause + '363				)364			and ( ' + @whereClauseSoFar + '365				)'366    END367    ELSE368    BEGIN369      SELECT @whereClause = 'where ' + @whereClauseSoFar370    END371    IF @ElementNummer = 0372    BEGIN373      SELECT @Erg = @Erg + @Pattern1374    END375    ELSE376    BEGIN377      SELECT @Erg = @Erg + 'union all' + CHAR(13) +CHAR(10)378    END379    SELECT @ElementNummer += 1380    IF @ChildTable IN('UNSAccountBHasUNSItemB',381    'UNSAccountBInUNSGroupB',382    'UNSGroupBExclusion',383    'UNSGroupBHasUnsItemB',384    'UNSGroupBInUNSGroupB',385    'UNSAccountBInUNSGroupB1',386    'UNSAccountBInUNSGroupB2',387    'UNSAccountBInUNSGroupB3',388    'UNSGroupB1Exclusion',389    'UNSGroupB2Exclusion',390    'UNSGroupB3Exclusion',391    'UNSGroupB1InUNSGroupB1',392    'UNSGroupB2InUNSGroupB2',393    'UNSGroupB3InUNSGroupB3')394    BEGIN395      SELECT396        @pattern2_33365 = REPLACE(@pattern2,397        '@v33365',398        'join UNSRootB RootTable on MemberTable.UID_UNSRootB = RootTable.UID_UNSRootB399													and RootTable.UID_DPRNameSpace =  ''@UID_DPRNameSpace''400													'401        )402    END403    ELSE404    BEGIN405      SELECT406        @pattern2_33365 = REPLACE(@pattern2,407        '@v33365',408        '--')409    END410    SELECT411      @Erg = @Erg + replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(REPLACE(@pattern2_33365412      , '@ContainerTable', @ContainerTable), '@ContainerColumn', @ContainerColumn), '@ChildConnectContainer',413      @ChildConnectContainer), '@MemberTable', @MemberTable), '@MemberColumn', @MemberColumn), '@ChildConnectMember',414      @ChildConnectMember), '@UID_DPRNameSpace', @UID_DPRNameSpace), '@OpSynonym', @OpSynonym), '@joinDeletedReal@',415      @joinDeletedReal), '@OpAddDel', @OpAddDel), '@whereClause', @whereClause), '@ChildTable', @ChildTable),416      '@ContainerObjectKey', @ContainerObjectKey),417      '@MemberObjectKey',418      @MemberObjectKey)419    IF @ErgSim = ''420    BEGIN421      SELECT422        @ErgSim = @ErgSim + replace(replace(replace(replace(@Pattern3, '@OpAddDel', replace(replace(@OpAddDel,423        '''ADD''', '''I'''), '''DEL''', '''D''')), '@ChildTable', @ChildTable), '@OpSynonym', @OpSynonym),424        '@joinDeletedReal@',425        @joinDeletedReal)426    END427    SELECT @ElementIndex += 1428  END429  IF @Erg = ''430  BEGIN431    GOTO Restposten31570432  END433  SELECT434    @Erg = CONCAT(@Erg,435    @ErgSim)436  IF @Operation = 'update'437  BEGIN438    SELECT @Erg = 'if 1 = 0' + CASE439    WHEN @IsXOriginHandling = 1 THEN440    '   or update(XOrigin)' + CHAR(13) +CHAR(10)441    ELSE ''442    END + + CASE443    WHEN @IsXIsInEffectHandling = 1 THEN444    '   or update(XIsInEffect)' + CHAR(13) +CHAR(10)445    ELSE ''446    END + + CASE447    WHEN @IsXMarkedForDeletionHandling = 1 THEN448    '   or update(XMarkedForDeletion)' + CHAR(13) +CHAR(10)449    ELSE ''450    END + '  begin' + CHAR(13) + CHAR(10) + dbo.QBM_FCVStringToIndent(@Erg,451    2) + '452	end -- if update()'453  END454  SELECT455    @Erg = '456----------------------------------------------------------457-- Handle ADD DEL DPRMemberShipAction (' + @ChildTable + ', ' + @Operation458    + ')459----------------------------------------------------------460 if dbo.QBM_FGISessionContext (''Fullsync'') = ''''461  begin' + CHAR(13) +CHAR462    (10) + dbo.QBM_FCVStringToIndent(@Erg,463    1) + '464  end -- if dbo.QBM_FGISessionContext(Fullsync)465----------------------------------------------------------466-- / Handle ADD DEL DPRMemberShipAction467----------------------------------------------------------'468    Restposten31570:469  SELECT470    TOP 1 @erg += CONCAT('471if update(XMarkedForDeletion)472 begin473	if exists (select top 1 1474				from deleted d join ',475    x.Childtable,476    ' mn on d.XObjectKey = mn.XObjectKey477								join DPRMemberShipAction m on mn.XObjectkey = m.ObjectKeyMN478				where d.XMarkedForDeletion & ',479    @StringPatternOutStanding,480    ' = 0481					and mn.XMarkedForDeletion & ',482    @StringPatternOutStanding,483    ' > 0484				)485	 begin486		delete DPRMemberShipAction487		from deleted d join ',488    x.Childtable,489    ' mn on d.XObjectKey = mn.XObjectKey490						join DPRMemberShipAction m on mn.XObjectkey = m.ObjectKeyMN491		where d.XMarkedForDeletion & ',492    @StringPatternOutStanding,493    ' = 0494			and mn.XMarkedForDeletion & ',495    @StringPatternOutStanding,496    ' > 0497	 end498end -- if update(XMarkedForDeletion)499')500  FROM(501  SELECT @Childtable AS Childtable502  FROM QBM_VQBMRelation r503  JOIN DPRNameSpaceHasDialogTable nht504    WITH(readpast)505    ON r.UID_DialogTableChild = nht.UID_DialogTable AND r.IsForUpdateXDateSubItem = 1506  JOIN QBM_VQBMRelation rm507    WITH(readpast)508    ON r.UID_QBMRelationMN = rm.UID_QBMRelation509  JOIN DialogTable tc510    WITH(readpast)511    ON r.ChildTable = tc.TableName AND tc.isMNTable = 1512  JOIN DialogColumn cd513    WITH(readpast)514    ON cd.UID_DialogTable = r.UID_DialogTableChild AND cd.ColumnName = 'XMarkedForDeletion'515  WHERE516    r.ChildTable = @ChildTable AND r.IsForUpdateXDateSubItem = 1 AND nht.IsAdHocSingleMemberShip = 1517  UNION518  SELECT @Childtable AS Childtable519  FROM QBM_VQBMRelation r520  JOIN DPRNameSpaceHasDialogTable nht521    WITH(readpast)522    ON r.UID_DialogTableChild = nht.UID_DialogTable AND r.IsForUpdateXDateSubItem = 1523  JOIN DialogColumn ok524    WITH(readpast)525    ON ok.UID_DialogTable = r.UID_DialogTableChild526  JOIN DialogValidDynamicRef rm527    WITH(readpast)528    ON ok.UID_DialogColumn = rm.UID_DialogColumn529  JOIN DialogTable tc530    WITH(readpast)531    ON ok.UID_DialogTable = tc.UID_DialogTable AND tc.IsMAllTable = 1532  JOIN DialogColumn cd533    WITH(readpast)534    ON cd.UID_DialogTable = r.UID_DialogTableChild AND cd.ColumnName = 'XMarkedForDeletion'535  WHERE536    r.ChildTable = @ChildTable AND r.IsForUpdateXDateSubItem = 1 AND nht.IsAdHocSingleMemberShip = 1) AS x Restposten441234:537  IF @Operation = 'delete' AND @IsXOriginHandling = 0538  BEGIN539    SELECT540      TOP 1 @erg += CONCAT('541		if exists (select top 1 1542					from deleted d join DPRMemberShipAction m on d.XObjectkey = m.ObjectKeyMN543					where d.XMarkedForDeletion & '544      ,545      @StringPatternOutStanding,546      ' > 0547					)548		 begin549			delete DPRMemberShipAction550			from deleted d join DPRMemberShipAction m on d.XObjectkey = m.ObjectKeyMN551			where d.XMarkedForDeletion & '552      ,553      @StringPatternOutStanding,554      ' > 0555		 end556	')557    FROM(558    SELECT @Childtable AS Childtable559    FROM QBM_VQBMRelation r560    JOIN DPRNameSpaceHasDialogTable nht561      WITH(readpast)562      ON r.UID_DialogTableChild = nht.UID_DialogTable AND r.IsForUpdateXDateSubItem = 1563    JOIN QBM_VQBMRelation rm564      WITH(readpast)565      ON r.UID_QBMRelationMN = rm.UID_QBMRelation566    JOIN DialogTable tc567      WITH(readpast)568      ON r.ChildTable = tc.TableName AND tc.isMNTable = 1569    JOIN DialogColumn cd570      WITH(readpast)571      ON cd.UID_DialogTable = r.UID_DialogTableChild AND cd.ColumnName = 'XMarkedForDeletion'572    WHERE573      r.ChildTable = @ChildTable AND r.IsForUpdateXDateSubItem = 1 AND nht.IsAdHocSingleMemberShip = 1574    UNION575    SELECT @Childtable AS Childtable576    FROM QBM_VQBMRelation r577    JOIN DPRNameSpaceHasDialogTable nht578      WITH(readpast)579      ON r.UID_DialogTableChild = nht.UID_DialogTable AND r.IsForUpdateXDateSubItem = 1580    JOIN DialogColumn ok581      WITH(readpast)582      ON ok.UID_DialogTable = r.UID_DialogTableChild583    JOIN DialogValidDynamicRef rm584      WITH(readpast)585      ON ok.UID_DialogColumn = rm.UID_DialogColumn586    JOIN DialogTable tc587      WITH(readpast)588      ON ok.UID_DialogTable = tc.UID_DialogTable AND tc.IsMAllTable = 1589    JOIN DialogColumn cd590      WITH(readpast)591      ON cd.UID_DialogTable = r.UID_DialogTableChild AND cd.ColumnName = 'XMarkedForDeletion'592    WHERE593      r.ChildTable = @ChildTable AND r.IsForUpdateXDateSubItem = 1 AND nht.IsAdHocSingleMemberShip = 1) AS x594  END595  endLabel:596  RETURN(@erg)597END
Open raw exported source
SQL · Raw260 lines
1  create   function dbo.DPR_FSQTriggerWatchMemberShip(@ChildTable varchar(30) , @Operation varchar(16) ) returns nvarchar(max) as begin declare 2@IsXOriginHandling bit = 0 declare @IsXIsInEffectHandling bit = 0 declare @IsXMarkedForDeletionHandling bit = 0 declare @Erg nvarchar(max) = '' declare3 @ErgSim nvarchar(max) = '' declare @ContainerTable varchar(30) declare @ContainerColumn varchar(30) declare @ChildConnectContainer varchar(30) declare4 @MemberTable varchar(30) declare @MemberColumn varchar(30) declare @ChildConnectMember varchar(30) declare @UID_ContainerTable varchar(38) declare @UID_DPRNameSpace5 varchar(38) declare @ContainerObjectKey nvarchar(1000) declare @MemberObjectKey nvarchar(1000)  declare @OpAddDel nvarchar(max) declare @OpSynonym varchar6(16) = 'deleted' declare @joinDeletedReal nvarchar(max) = '--' declare @whereClauseSoFar nvarchar(max) = '1=1' declare @whereClause nvarchar(max) declare7 @StringPatternFullsync nvarchar(100) declare @StringPatternOutStanding nvarchar(100) declare @ElementNummer int = 0 declare @DebugSwitch int = 0 declare8 @DebugMessage nvarchar(max) declare @DebugOnly nvarchar(16) = '--Debug ' declare @crlf nvarchar(20) = nchar(13) + nchar(10) declare @AdditionalWhereClause9 nvarchar(max) declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @Pattern1 nvarchar(max) ='10insert into DPRMemberShipAction	(11	 UID_DPRMemberShipAction12	, ObjectKeyMN  13	, Operation 14	, ObjectKeyBase 15	, ObjectKeyMember 16	, UID_DPRNameSpace17	, OperationDate18	, XObjectKey19			)20'21 declare @Pattern2 nvarchar(max) ='22select 	 y.UID_DPRMemberShipAction23	, y.ObjectKeyMN  24	, y.Operation 25	, y.ObjectKeyBase 26	, y.ObjectKeyMember 27	, y.UID_DPRNameSpace28	, y.OperationDate29	, dbo.QBM_FCVElementToObjectKey1(''DPRMemberShipAction'', ''UID_DPRMemberShipAction'', y.UID_DPRMemberShipAction)30from (31	 select 32		i.XObjectKey as ObjectKeyMN33		, @OpAddDel as Operation  34		, @ContainerObjectKey as ObjectKeyBase35		, @MemberObjectKey as ObjectKeyMember36		, ''@UID_DPRNameSpace'' as UID_DPRNameSpace37		, @TrackingDate as OperationDate38		, newid() as UID_DPRMemberShipAction39		from @OPSynonym i -- @ChildTable40				@joinDeletedReal@41		join @Containertable ContainerTable on i.@ChildConnectContainer = ContainerTable.@ContainerColumn42						join @MemberTable MemberTable on i.@ChildConnectMember = MemberTable.@MemberColumn43						@v3336544		@WhereClause45	) as y46'47 declare @pattern2_33365 nvarchar(max) declare @Pattern3 nvarchar(max) = '48 49	if @IsSimulationMode = 150	 begin5152		delete @SimulationModeBuffer5354		insert into @SimulationModeBuffer (operation, BaseObjectType, ColumnName, Objectkey, OldValue) 55		select ''I'', ''DPRMemberShipAction'', ''UID_DPRMemberShipAction'', m.XObjectKey , '''' 56		from DPRMemberShipAction m57			where m.OperationDate = @TrackingDate58		union all59		select ''I'', ''DPRMemberShipAction'', ''ObjectKeyMN'', m.XObjectKey , '''' 60		from DPRMemberShipAction m61			where m.OperationDate = @TrackingDate62		union all63		select ''I'', ''DPRMemberShipAction'', ''Operation'', m.XObjectKey , '''' 64		from DPRMemberShipAction m65			where m.OperationDate = @TrackingDate66		union all67		select ''I'', ''DPRMemberShipAction'', ''ObjectKeyBase'', m.XObjectKey , '''' 68		from DPRMemberShipAction m69			where m.OperationDate = @TrackingDate70		union all71		select ''I'', ''DPRMemberShipAction'', ''ObjectKeyMember'', m.XObjectKey , '''' 72		from DPRMemberShipAction m73			where m.OperationDate = @TrackingDate74		union all75		select ''I'', ''DPRMemberShipAction'', ''UID_DPRNameSpace'', m.XObjectKey , '''' 76		from DPRMemberShipAction m77			where m.OperationDate = @TrackingDate78		union all79		select ''I'', ''DPRMemberShipAction'', ''OperationDate'', m.XObjectKey , '''' 80		from DPRMemberShipAction m81			where m.OperationDate = @TrackingDate82		union all83		select ''I'', ''DPRMemberShipAction'', ''ObjectKeyMN'', m.XObjectKey , '''' 84		from DPRMemberShipAction m85			where m.OperationDate = @TrackingDate8687		exec QBM_PFESimulationFill @SimulationModeBuffer8889	 end '90   select @StringPatternOutStanding = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|',0)), 0) if not exists91 (select top 1 1 from DialogTable t with (readpast) where t.TableName = @ChildTable and ( t.isMNTable = 1 or t.IsMAllTable = 1 ) ) begin if @DebugSwitch92 > 0 begin select @DebugMessage = @DebugOnly + ' keine m:n oder m:all ' + @ChildTable  select @Erg = @DebugMessage end goto endLabel end select @IsXMarkedForDeletionHandling93 = dbo.QBM_FGIColumnExists(@ChildTable, 'XMarkedForDeletion') select @IsXOriginHandling = dbo.QBM_FGIColumnExists(@ChildTable, 'XOrigin') if @IsXOriginHandling94 = 1 begin select @IsXIsInEffectHandling = dbo.QBM_FGIColumnExists(@ChildTable, 'XIsInEffect') end  if @Operation = 'insert' begin select @OpSynonym = 95'inserted' select @OpAddDel = '''ADD''' if @IsXOriginHandling = 1 begin select @whereClauseSoFar = ' i.XOrigin > 0' end if @IsXIsInEffectHandling = 1 begin96 select @whereClauseSoFar = @whereClauseSoFar + ' and i.XIsInEffect = 1' end end if @Operation = 'delete' begin select @OpAddDel = '''DEL''' if @IsXOriginHandling97 = 1 begin if @DebugSwitch > 0 begin select @DebugMessage = @DebugOnly + ' dann passiert beim Delete nix mehr' + @ChildTable  select @Erg = @DebugMessage98 end goto Restposten441234 end end if @Operation = 'Update' begin if @IsXOriginHandling = 0 and @IsXMarkedForDeletionHandling = 0 begin if @DebugSwitch99 > 0 begin select @DebugMessage = @DebugOnly + ' dann passiert alles im Delete, brauchen wir kein update ' + @ChildTable  select @Erg = @DebugMessage end100 goto endLabel end    select @whereClauseSoFar = @crlf + '				' + case @IsXOriginHandling * 100 + @IsXIsInEffectHandling *10 + @IsXMarkedForDeletionHandling101  when 001 then ' i.XMarkedForDeletion & ' + @StringPatternOutStanding + ' > 0 and t.XMarkedForDeletion & ' + @StringPatternOutStanding + ' = 0 '    when102 100 then 'dbo.QBM_FGIXOriginChanged_Effect(i.XOrigin, t.XOrigin, 1, 1) = 1'     when 101 then '( i.XOrigin > 0 and t.XOrigin = 0 and t.XMarkedForDeletion & '103 + @StringPatternOutStanding + ' = 0 and i.XMarkedForDeletion & ' + @StringPatternOutStanding + ' = 0 )104							or ( i.XOrigin = 0 and t.XOrigin > 0	)105							or (i.XMarkedForDeletion & '106 + @StringPatternOutStanding + ' > 0 107								and t.XMarkedForDeletion & ' + @StringPatternOutStanding + ' = 0 108								and t.XOrigin > 0109							)' 110when 110 then 'dbo.QBM_FGIXOriginChanged_Effect(i.XOrigin, t.XOrigin, i.XIsInEffect, t.XIsInEffect) = 1'     when 111 then concat( '( i.XIsInEffect > 0 and t.XIsInEffect = 0 and t.XMarkedForDeletion & '111 , @StringPatternOutStanding , ' = 0 and i.XMarkedForDeletion & ' + @StringPatternOutStanding + ' = 0 )112						or ( i.XOrigin > 0 and t.XOrigin = 0 and i.XIsInEffect > 0  and t.XMarkedForDeletion & '113 + @StringPatternOutStanding + ' = 0 and i.XMarkedForDeletion & ' + @StringPatternOutStanding + ' = 0 )114						 or ( i.XIsInEffect = 0 and t.XIsInEffect > 0 and t.XMarkedForDeletion & '115, @StringPatternOutStanding , ' = 0 	)116						 or (i.XMarkedForDeletion & ' , @StringPatternOutStanding , ' > 0 117								and t.XMarkedForDeletion & ' 118, @StringPatternOutStanding , ' = 0 119								and t.XIsInEffect > 0120								and t.XOrigin > 0121							)' )   else ' 1 = 0' end     select @OpAddDel = 122'			' + case @IsXOriginHandling * 100 + @IsXIsInEffectHandling *10 + @IsXMarkedForDeletionHandling  when 001 then 'case 123					when t.XMarkedForDeletion & '124 + @StringPatternOutStanding + ' = 0 125						then ''ADD''126						else ''DEL''127				   end'    when 100 then 'case 128					when (t.XOrigin > 0 and i.XOrigin = 0 )129						then ''ADD''130					else ''DEL''131				   end'132 when 101 then 'case 133					when (t.XOrigin > 0 and i.XOrigin = 0 )134						or ( i.XMarkedForDeletion & ' + @StringPatternOutStanding + ' > 0 135								and t.XMarkedForDeletion & '136 + @StringPatternOutStanding + ' = 0 137								and t.XOrigin > 0138							)139						then ''ADD''140					else ''DEL''141				   end' when 110 then 'case 142					when (t.XOrigin > 0 and t.XIsInEffect = 1 and i.XIsInEffect = 0 )143						 then ''ADD''144					else ''DEL''145				   end'146 when 111 then concat('case 147					when (t.XOrigin > 0 and t.XIsInEffect = 1 and i.XIsInEffect = 0 and t.XMarkedForDeletion & ', @StringPatternOutStanding148 , ' = 0 )149						 or ( i.XMarkedForDeletion & ' , @StringPatternOutStanding , ' > 0 150								and t.XMarkedForDeletion & ' , @StringPatternOutStanding151 , ' = 0 152								and t.XOrigin > 0153							)	154						 then ''ADD''155					else ''DEL''156				   end' )   else '  ''ADD'' ' end select @joinDeletedReal 157= ' join ' + @ChildTable + ' t on ' + dbo.QBM_FSQTableJoin ( @ChildTable , 'i', 't') end  insert into @ElementBuffer (Ident1  , Ident2  , Ident3  , UID2158  , UID3  , UID4  , UID1  , ContentFull  , ObjectKey1  , ObjectKey2  ) select r.ParentTable as ContainerTable , r.ParentColumn as ContainerColumn , r.ChildColumn159 as ChildConnectContainer , rm.ParentTable as MemberTable , rm.ParentColumn as MemberColumn , rm.ChildColumn as ChildConnectMember , nht.UID_DPRNameSpace160 , nht.WhereClause as AdditionalWhereClause    , 'ContainerTable.XObjectKey' as ContainerObjectKey , 'MemberTable.XObjectKey' as MemberObjectKey from QBM_VQBMRelation161 r join DPRNameSpaceHasDialogTable nht with (readpast) on r.UID_DialogTableChild = nht.UID_DialogTable and r.IsForUpdateXDateSubItem = 1 join QBM_VQBMRelation162 rm on r.UID_QBMRelationMN = rm.UID_QBMRelation join DialogTable tc with (readpast) on r.ChildTable = tc.TableName and tc.isMNTable = 1 where r.ChildTable163 = @ChildTable and r.IsForUpdateXDateSubItem = 1 and nht.IsAdHocSingleMemberShip = 1 union select r.ParentTable as ContainerTable , r.ParentColumn as ContainerColumn164 , r.ChildColumn as ChildConnectContainer  , dbo.QBM_FGITableName(rm.UID_DialogTableReference) as MemberTable , 'XObjectKey' as MemberColumn , ok.ColumnName165 as ChildConnectMember , nht.UID_DPRNameSpace , nht.WhereClause as AdditionalWhereClause   , 'ContainerTable.XObjectKey' as ContainerObjectKey , 'i.' +166 ok.ColumnName as MemberObjectKey from QBM_VQBMRelation r join DPRNameSpaceHasDialogTable nht with (readpast) on r.UID_DialogTableChild = nht.UID_DialogTable167 and r.IsForUpdateXDateSubItem = 1 join DialogColumn ok with (readpast) on ok.UID_DialogTable = r.UID_DialogTableChild join DialogValidDynamicRef rm with168 (readpast) on ok.UID_DialogColumn = rm.UID_DialogColumn join DialogTable tc with (readpast) on ok.UID_DialogTable = tc.UID_DialogTable and tc.IsMAllTable169 = 1 where r.ChildTable = @ChildTable and r.IsForUpdateXDateSubItem = 1 and nht.IsAdHocSingleMemberShip = 1 select @ElementCount = @@ROWCOUNT select @ElementIndex170 = 1 while @ElementIndex <= @ElementCount begin select top 1 @ContainerTable = bu.Ident1  , @ContainerColumn = bu.Ident2  , @ChildConnectContainer = bu.Ident3171  , @MemberTable = bu.UID2  , @MemberColumn = bu.UID3  , @ChildConnectMember = bu.UID4  , @UID_DPRNameSpace = bu.UID1  , @AdditionalWhereClause = bu.ContentFull172  , @ContainerObjectKey = bu.ObjectKey1  , @MemberObjectKey = bu.ObjectKey2  from @ElementBuffer bu where bu.ElementIndex = @ElementIndex if @AdditionalWhereClause173 > ' ' begin select @whereClause = '174			where ( ' + @AdditionalWhereClause + '175				)176			and ( ' + @whereClauseSoFar + '177				)' end else begin select178 @whereClause = 'where ' + @whereClauseSoFar end               if @ElementNummer = 0 begin select @Erg = @Erg + @Pattern1 end else begin select @Erg = 179@Erg + 'union all' + CHAR(13) +CHAR(10) end select @ElementNummer += 1    if @ChildTable in ( 'UNSAccountBHasUNSItemB' , 'UNSAccountBInUNSGroupB' , 'UNSGroupBExclusion'180 , 'UNSGroupBHasUnsItemB' , 'UNSGroupBInUNSGroupB'  , 'UNSAccountBInUNSGroupB1' , 'UNSAccountBInUNSGroupB2' , 'UNSAccountBInUNSGroupB3' , 'UNSGroupB1Exclusion'181 , 'UNSGroupB2Exclusion' , 'UNSGroupB3Exclusion' , 'UNSGroupB1InUNSGroupB1' , 'UNSGroupB2InUNSGroupB2' , 'UNSGroupB3InUNSGroupB3'  ) begin select @pattern2_33365182 = REPLACE(@pattern2, '@v33365', 'join UNSRootB RootTable on MemberTable.UID_UNSRootB = RootTable.UID_UNSRootB183													and RootTable.UID_DPRNameSpace =  ''@UID_DPRNameSpace''184													'185 ) end else begin select @pattern2_33365 = REPLACE(@pattern2, '@v33365', '--' ) end  select @Erg = @Erg + replace(replace(replace(replace(replace(replace186(replace(replace(replace(replace(replace(replace(replace(REPLACE(@pattern2_33365 ,'@ContainerTable',@ContainerTable) ,'@ContainerColumn', @ContainerColumn187) ,'@ChildConnectContainer', @ChildConnectContainer) ,'@MemberTable', @MemberTable) ,'@MemberColumn', @MemberColumn) ,'@ChildConnectMember', @ChildConnectMember188) ,'@UID_DPRNameSpace', @UID_DPRNameSpace) , '@OpSynonym', @OpSynonym) , '@joinDeletedReal@', @joinDeletedReal) , '@OpAddDel', @OpAddDel) , '@whereClause'189, @whereClause) , '@ChildTable', @ChildTable) , '@ContainerObjectKey', @ContainerObjectKey) , '@MemberObjectKey', @MemberObjectKey)     if @ErgSim = ''190 begin select @ErgSim = @ErgSim + replace(replace(replace(replace(@Pattern3 , '@OpAddDel', replace(replace(@OpAddDel , '''ADD''' , '''I''') , '''DEL'''191, '''D''')) , '@ChildTable', @ChildTable) , '@OpSynonym', @OpSynonym) , '@joinDeletedReal@', @joinDeletedReal) end  select @ElementIndex += 1 end  if @Erg192 = '' begin     goto Restposten31570 end select @Erg = concat(@Erg , @ErgSim) if @Operation = 'update' begin select @Erg = 'if 1 = 0' + case when @IsXOriginHandling193 = 1 then '   or update(XOrigin)' + CHAR(13) +CHAR(10) else '' end + + case when @IsXIsInEffectHandling = 1 then '   or update(XIsInEffect)' + CHAR(13)194 +CHAR(10) else '' end + + case when @IsXMarkedForDeletionHandling = 1 then '   or update(XMarkedForDeletion)' + CHAR(13) +CHAR(10) else '' end + '  begin'195 + CHAR(13) + CHAR(10) + dbo.QBM_FCVStringToIndent(@Erg, 2) + '196	end -- if update()' end select @Erg = '197----------------------------------------------------------198-- Handle ADD DEL DPRMemberShipAction ('199 + @ChildTable + ', ' + @Operation + ')200----------------------------------------------------------201 if dbo.QBM_FGISessionContext (''Fullsync'') = ''''202  begin'203 + CHAR(13) +CHAR(10) + dbo.QBM_FCVStringToIndent(@Erg, 1) + '204  end -- if dbo.QBM_FGISessionContext(Fullsync)205----------------------------------------------------------206-- / Handle ADD DEL DPRMemberShipAction207----------------------------------------------------------'208 Restposten31570:  select top 1 @erg += concat( '209if update(XMarkedForDeletion)210 begin211	if exists (select top 1 1212				from deleted d join ', x.Childtable213 , ' mn on d.XObjectKey = mn.XObjectKey214								join DPRMemberShipAction m on mn.XObjectkey = m.ObjectKeyMN215				where d.XMarkedForDeletion & ' , @StringPatternOutStanding216 , ' = 0217					and mn.XMarkedForDeletion & ' , @StringPatternOutStanding , ' > 0218				)219	 begin220		delete DPRMemberShipAction221		from deleted d join '222, x.Childtable , ' mn on d.XObjectKey = mn.XObjectKey223						join DPRMemberShipAction m on mn.XObjectkey = m.ObjectKeyMN224		where d.XMarkedForDeletion & '225 , @StringPatternOutStanding , ' = 0226			and mn.XMarkedForDeletion & ' , @StringPatternOutStanding , ' > 0227	 end228end -- if update(XMarkedForDeletion)229'230 ) from ( select @Childtable as Childtable from QBM_VQBMRelation r join DPRNameSpaceHasDialogTable nht with (readpast) on r.UID_DialogTableChild = nht.UID_DialogTable231 and r.IsForUpdateXDateSubItem = 1 join QBM_VQBMRelation rm with (readpast) on r.UID_QBMRelationMN = rm.UID_QBMRelation join DialogTable tc with (readpast232) on r.ChildTable = tc.TableName and tc.isMNTable = 1 join DialogColumn cd with (readpast) on cd.UID_DialogTable = r.UID_DialogTableChild and cd.ColumnName233 = 'XMarkedForDeletion' where r.ChildTable = @ChildTable and r.IsForUpdateXDateSubItem = 1 and nht.IsAdHocSingleMemberShip = 1 union select @Childtable234 as Childtable from QBM_VQBMRelation r join DPRNameSpaceHasDialogTable nht with (readpast) on r.UID_DialogTableChild = nht.UID_DialogTable and r.IsForUpdateXDateSubItem235 = 1 join DialogColumn ok with (readpast) on ok.UID_DialogTable = r.UID_DialogTableChild join DialogValidDynamicRef rm with (readpast) on ok.UID_DialogColumn236 = rm.UID_DialogColumn join DialogTable tc with (readpast) on ok.UID_DialogTable = tc.UID_DialogTable and tc.IsMAllTable = 1 join DialogColumn cd with 237(readpast) on cd.UID_DialogTable = r.UID_DialogTableChild and cd.ColumnName = 'XMarkedForDeletion' where r.ChildTable = @ChildTable and r.IsForUpdateXDateSubItem238 = 1 and nht.IsAdHocSingleMemberShip = 1 ) as x  Restposten441234: if @Operation = 'delete' and @IsXOriginHandling = 0 begin select top 1 @erg += concat239( '240		if exists (select top 1 1241					from deleted d join DPRMemberShipAction m on d.XObjectkey = m.ObjectKeyMN242					where d.XMarkedForDeletion & ' ,243 @StringPatternOutStanding , ' > 0244					)245		 begin246			delete DPRMemberShipAction247			from deleted d join DPRMemberShipAction m on d.XObjectkey = m.ObjectKeyMN248			where d.XMarkedForDeletion & '249 , @StringPatternOutStanding , ' > 0250		 end251	' ) from ( select @Childtable as Childtable from QBM_VQBMRelation r join DPRNameSpaceHasDialogTable nht 252with (readpast) on r.UID_DialogTableChild = nht.UID_DialogTable and r.IsForUpdateXDateSubItem = 1 join QBM_VQBMRelation rm with (readpast) on r.UID_QBMRelationMN253 = rm.UID_QBMRelation join DialogTable tc with (readpast) on r.ChildTable = tc.TableName and tc.isMNTable = 1 join DialogColumn cd with (readpast) on cd.UID_DialogTable254 = r.UID_DialogTableChild and cd.ColumnName = 'XMarkedForDeletion' where r.ChildTable = @ChildTable and r.IsForUpdateXDateSubItem = 1 and nht.IsAdHocSingleMemberShip255 = 1 union select @Childtable as Childtable from QBM_VQBMRelation r join DPRNameSpaceHasDialogTable nht with (readpast) on r.UID_DialogTableChild = nht.UID_DialogTable256 and r.IsForUpdateXDateSubItem = 1 join DialogColumn ok with (readpast) on ok.UID_DialogTable = r.UID_DialogTableChild join DialogValidDynamicRef rm with257 (readpast) on ok.UID_DialogColumn = rm.UID_DialogColumn join DialogTable tc with (readpast) on ok.UID_DialogTable = tc.UID_DialogTable and tc.IsMAllTable258 = 1 join DialogColumn cd with (readpast) on cd.UID_DialogTable = r.UID_DialogTableChild and cd.ColumnName = 'XMarkedForDeletion' where r.ChildTable = 259@ChildTable and r.IsForUpdateXDateSubItem = 1 and nht.IsAdHocSingleMemberShip = 1 ) as x end  endLabel:     return(@erg) end 260