dbo.QBM_ZXMarkedForDeletionPush

1        create   procedure QBM_ZXMarkedForDeletionPush (@SlotNumber int ) AS begin declare @UID_DialogTable varchar(38) declare @ParentTableName
2 varchar(30) declare @ChildTableName varchar(30) declare @ParentColumnName varchar(30) declare @ChildColumnName varchar(30) declare @GenProcID varchar(38
3) declare @MyXUser nvarchar(64) = object_name(@@procid) declare @cmd nvarchar(max) declare @PatternNeg varchar(64) declare @Pattern varchar(64) declare
4 @DebugSwitch int = 0 declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser'
5) declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DBQueueCurrent QBM_YDBQueueCurrent BEGIN TRY insert
6 into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID
7 from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end select @PatternNeg = dbo.QBM_FCVBinaryToString
8( CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|OutStanding|', 1)), 0) select @Pattern = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, 
9dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|', 0)), 0) if @DebugSwitch > 0 begin print @PatternNeg print @Pattern end insert into @ElementBuffer (
10 UID1 , UID2  , Ident1  , Ident2  , Ident3  , ObjectKey1  ) select cu.UID_SubParameter  , MAX(cu.GenProcID) , dbo.QBM_FGITableName(cu.UID_SubParameter 
11 ) as ParentTableName , cpk.ColumnName as ParentColumnName , tc.TableName as ChildTableName , cci.ColumnName as ChildColumnName from @DBQueueCurrent cu
12 join DialogColumn cpk with (readpast) on cu.UID_SubParameter  = cpk.UID_DialogTable and cpk.IsPKMember = 1  join DialogColumn ca with (readpast) on cu.UID_SubParameter
13  = ca.UID_DialogTable and ca.ColumnName = 'XDateSubItem'  join DialogColumn cx with (readpast) on cu.UID_SubParameter  = cx.UID_DialogTable and cx.ColumnName
14 = 'XMarkedForDeletion' join QBMRelation r with (readpast) on cpk.UID_DialogColumn = r.UID_ParentColumn and r.IsForUpdateXDateSubItem = 1 join DialogColumn
15 cci with (readpast) on r.UID_ChildColumn = cci.UID_DialogColumn join DialogTable tc with (readpast) on cci.UID_DialogTable = tc.UID_DialogTable  join 
16DialogColumn ccx with (readpast) on tc.UID_DialogTable = ccx.UID_DialogTable and ccx.ColumnName = 'XMarkedForDeletion' group by cu.UID_SubParameter  , 
17cpk.ColumnName, tc.TableName, cci.ColumnName select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select
18 top 1 @UID_DialogTable = bu.UID1 , @GenProcID = bu.UID2  , @ParentTableName = bu.Ident1  , @ParentColumnName = bu.Ident2  , @ChildTableName = bu.Ident3
19  , @ChildColumnName = bu.ObjectKey1  from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select @cmd = concat('
20	updat','e ' , @ChildTableName
21 , '
22		set XMarkedForDeletion = (z.XMarkedForDeletion )| (x.XMarkedForDeletion & ' , @Pattern , ') ' , case when dbo.QBM_FGIColumnExistsInSchema(@ChildTableName
23, 'XDateUpdated') = 1 then concat('' , '	--34101
24								, XDateUpdated = getutcdate()
25								, XUserUpdated = ''', object_name(@@procid) , '''
26								'
27) else '' end , ' from QBMDBQueueCurrent cu with (readpast) join ' , @ParentTableName , '  x on cu.UID_SubParameter = ''' , @UID_DialogTable , '''
28													and cu.UID_Parameter = x.'
29 , @ParentColumnName , '
30													and cu.SlotNumber = ' , str(@SlotNumber) , ' 
31								join ' , @ChildTableName , ' z on z.' , @ChildColumnName 
32, ' = x.' , @ParentColumnName , '
33		where (z.XMarkedForDeletion & ' , @Pattern , ') 
34			<>  (x.XMarkedForDeletion & ' , @Pattern , ')								
35		' ) 
36if @DebugSwitch > 0 begin print @cmd end exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @MyXUser exec QBM_PExecuteSQLWithRetry_LLP
37 @SQLStatement = @cmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 select @ElementIndex
38 += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH EndLabel: exec QBM_PSessionContextSet 'GenProcID'
39, @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R return end 
40