dbo.QBM_PMNTableDelete

1     create   procedure QBM_PMNTableDelete (@TargetTable varchar(30)  , @ElementColumn varchar(30) , @AssignedElementColumn varchar(30) , @TargetIsView
2 bit = 0   , @FKTableNameElement varchar(30) = ''  , @FKColumnNameElement varchar(30) = ''      ) as begin declare @GenProcID varchar(38) declare @SQLCmdDelete
3 nvarchar(max) = N'' declare @SQLCmdFire nvarchar(max) = N'' declare @SQLAction nvarchar(max)  declare @TargetTableView varchar(30)  declare @IsAssignmentWithEvent
4 bit declare @DebugSwitch int = 0 declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext
5('XUser') declare @ElementCount int declare @ElementIndex int declare @DifferentTables QBM_YCursorBuffer declare @ElementCountDifferentTables int declare
6 @ElementIndexDifferentTables int declare @ElementLastDifferentTables int SET XACT_ABORT OFF BEGIN TRY select @SQLCmdDelete = concat('
7	  delete ' , @TargetTable
8 , '
9		from ' , @TargetTable , ' z , #QBMDeltaDelete  d 
10		where d.GenProcID = ''@GenProciD''
11		 and z.' , @ElementColumn , ' = d.element
12		 and z.'
13 , @AssignedElementColumn , ' = d.AssignedElement
14		' ) if @TargetIsView = 0 begin select @SQLCmdFire = '
15		declare @EntriesToFire QBM_YParameterList 
16		declare @AddonElements QBM_YParameterList 
17			
18		insert into @EntriesToFire(Parameter1)
19			select @XObjectKey@
20			from #QBMDeltaDelete d
21			where d.GenProcID = ''@GenProciD''
22				'
23 if @FKTableNameElement > ' ' and @FKColumnNameElement > ' ' begin select @SQLCmdFire =concat( @SQLCmdFire , '
24				
25			insert into @AddonElements(Parameter1, Parameter2)
26				select distinct x.XObjectKey, @XObjectKey@
27				from #QBMDeltaDelete d join '
28 , @FKTableNameElement , ' x on x.' , @FKColumnNameElement , ' = d.Element
29				where d.GenProcID = ''@GenProciD''
30			' ) end  select @SQLCmdFire = concat
31(@SQLCmdFire , '		
32
33		exec QBM_PJobCreate_HODelete_L ''' , @TargetTable , ''', @EntriesToFire, ''@GenProcID''
34						, @AddonElements
35						, @isToFreezeOnError = 1
36
37				'
38 ) end else begin select @SQLCmdFire = '
39		declare @EntriesToFire QBM_YParameterList 
40		declare @AddonElements QBM_YParameterList 
41			
42		insert into @EntriesToFire(Parameter1)
43			select @XObjectKey@
44			from #QBMDeltaDelete d
45			where d.GenProcID = ''@GenProciD''
46			 and d.TargetTableView = ''@TargetTableView@''
47				'
48 if @FKTableNameElement > ' ' and @FKColumnNameElement > ' ' begin select @SQLCmdFire = concat(@SQLCmdFire , '
49				
50			insert into @AddonElements(Parameter1, Parameter2)
51				select distinct x.XObjectKey, @XObjectKey@
52				from #QBMDeltaDelete d join '
53 , @FKTableNameElement , ' x on x.' , @FKColumnNameElement , ' = d.Element
54				where d.GenProcID = ''@GenProciD''
55			' ) end  select @SQLCmdFire = concat
56(@SQLCmdFire , '		
57
58		exec QBM_PJobCreate_HODelete_L ''@TargetTableView@'', @EntriesToFire, ''@GenProcID''
59						, @AddonElements
60						, @isToFreezeOnError = 1
61				'
62 ) end if @DebugSwitch > 0 begin print @sqlcmdFire print @sqlcmdDelete end declare @GenProcIDs_Delete QBM_YCursorBuffer insert into @GenProcIDs_Delete 
63(UID1) select distinct d.GenProcID from #QBMDeltaDelete d select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount
64 begin select top 1 @GenProcID = bu.UID1 from @GenProcIDs_Delete bu where bu.ElementIndex = @ElementIndex  exec QBM_PSessionContextSet 'GenProcID', @GenProcID
65 if @TargetIsView = 0 begin select top 1 @IsAssignmentWithEvent = t.isAssignmentWithEvent from DialogTable t with (readpast) where t.TableName = @TargetTable
66 if @IsAssignmentWithEvent = 0 begin select @SQLAction = REPLACE(@SQLCmdDelete , '@GenProcID', @GenProcID) end else begin select @SQLAction = replace(REPLACE
67(@SQLCmdFire , '@GenProcID', @GenProcID) , '@XObjectKey@', concat('dbo.QBM_FCVElementToObjectKey2( ''' , @TargetTable , ''' , ''' , @ElementColumn , ''' , d.Element, '''
68 , @AssignedElementColumn , ''',  d.AssignedElement)') ) end   exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLAction , @LockTimeout_ms = default
69 , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = default , @HandleErrorSilent = 0 end else begin  delete  @DifferentTables  insert into @DifferentTables
70 (UID1, Bit1) select distinct d.TargetTableView, t.isAssignmentWithEvent from #QBMDeltaDelete d join DialogTable t with (readpast) on d.TargetTableView
71 = t.TableName select @ElementCountDifferentTables = @@ROWCOUNT select @ElementIndexDifferentTables = @@IDENTITY - @ElementCount +1 select @ElementLastDifferentTables
72 = @@IDENTITY while @ElementIndexDifferentTables <= @ElementLastDifferentTables begin select top 1 @TargetTableView = bu.UID1 , @isAssignmentWithEvent 
73= bu.Bit1 from @DifferentTables bu where bu.ElementIndex = @ElementIndexDifferentTables if @isAssignmentWithEvent = 1 begin select @SQLAction = replace
74(replace(REPLACE(@SQLCmdFire , '@GenProcID', @GenProcID) , '@TargetTableView@', @TargetTableView) , '@XObjectKey@', 'dbo.QBM_FCVElementToObjectKey2( d.TargetTableView , d.ElementColumnView , d.Element, d.AssignedElementColumnView,  d.AssignedElement)'
75) end else begin select @SQLAction = REPLACE(@SQLCmdDelete , '@GenProcID', @GenProcID) end  exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLAction
76 , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = default , @HandleErrorSilent = 0 select @ElementIndexDifferentTables
77 += 1 end  end  select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow
78() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser'
79, @XUser_R return end 
80