dbo.QBM_ZPreProptableFK

1      create   procedure QBM_ZPreProptableFK (@SlotNumber int) as begin  declare @GenProcID varchar(38) declare @CountItems int  declare @GenProcID_R
2 varchar(38) = dbo.QBM_FGISessionContext('') declare @DBQueueCurrent QBM_YDBQueueCurrent declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate
3 datetime = getutcdate() declare @UID_TaskFollow varchar(38) BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, 
4GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber
5 if @@rowcount = 0 begin goto EndLabel end declare @PrePropHelper QBM_YPrepropHelper insert into @PrePropHelper (uid_parameter , IsDeactivated , GenProcID
6 , PreProcessorCondition, IsDeactivatedOld) select x.uid_parameter, 0, x.GenProcID, isnull(t.PreProcessorCondition,N''), t.isDeactivatedByPreProcessor 
7from @DBQueueCurrent x join dialogTable t on x.uid_parameter = t.TableName update @PrePropHelper set IsDeactivated = dbo.QBM_FGIPrepropConditionDeactiv
8(h.PreProcessorCondition) from @PrePropHelper h where h.PreProcessorCondition > ' '   update @PrePropHelper set IsDeactivated = 1  from @PrePropHelper 
9h join (select t.TableName, count(*) as CountItems from dialogcolumn c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable where c.IsPKMember =
10 1 group by t.TableName ) as k1 on k1.TableName = h.uid_parameter left outer join (select t.TableName, count(*) as CountItems from dialogcolumn c join 
11DialogTable t on c.UID_DialogTable = t.UID_DialogTable where c.IsPKMember = 1 and isnull(c.IsDeactivatedByPreProcessor,0) = 0 group by t.TableName ) as
12 k2 on k1.TableName = k2.TableName where h.IsDeactivated = 0 and isnull(k1.CountItems,0) <> isnull(k2.CountItems,0)    update @PrePropHelper set IsDeactivated
13 = 1 from @PrePropHelper h join dialogtable t on h.uid_parameter = t.TableName join dialogtable b on t.UID_DialogTableBase = b.UID_DialogTable where b.isDeactivatedByPreprocessor
14 = 1    select @GenProcID = max(GenProcID) from @DBQueueCurrent where GenProcID > ' ' exec QBM_PSessionContextSet 'GenProcID', @GenProcID update DialogTable
15 set IsDeactivatedByPreProcessor = x.isDeactivated , XDateUpdated = @Xdate , XUserUpdated = @XUser  from DialogTable r join @PrePropHelper x on r.TableName
16 = x.uid_parameter where r.IsDeactivatedByPreProcessor <> x.IsDeactivated select @CountItems = @@rowcount     update DialogColumn set IsDeactivatedByPreProcessor
17 = 1 , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogColumn c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable join @PrePropHelper
18 x on x.UID_Parameter = t.TableName where x.IsDeactivated = 1 and c.IsDeactivatedByPreProcessor = 0  if @CountItems > 0 begin    declare @DBQueueElements_01
19 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select distinct t.TableName as UID
20, x.GenProcID from DialogTable t join DialogTable b on t.UID_DialogTableBase = b.UID_DialogTable join @PrePropHelper x on b.TableName = x.uid_parameter
21 and x.IsDeactivated <> x.IsDeactivatedOld ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTableFK', @DBQueueElements_01   update DialogColumn 
22set IsDeactivatedByPreProcessor = 1 , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogColumn c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable
23 join @PrePropHelper x on x.uid_parameter = t.TableName where x.IsDeactivatedOld = 0 and x.IsDeactivated = 1 and c.IsDeactivatedByPreProcessor = 0 declare
24 @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select distinct
25 c.UID_DialogColumn as UID, x.GenProcID from DialogColumn c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable join @PrePropHelper x on t.TableName
26 = x.uid_parameter   and x.IsDeactivated = 0 and x.IsDeactivatedOld = 1 and c.isPKMember = 0 ) as x  select @UID_TaskFollow = 'QBM-K-CommonPrepropColumn'
27 exec QBM_PDBQueueInsert_Bulk @UID_TaskFollow, @DBQueueElements_02  declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03 (object
28, subobject, genprocid) select x.uid, null, @GenProcID from (select distinct c.UID_DialogColumn as UID, x.GenProcID from DialogColumn c join DialogTable
29 t on c.UID_DialogTable = t.UID_DialogTable join @PrePropHelper x on t.TableName = x.uid_parameter and x.IsDeactivated <> x.IsDeactivatedOld and c.ispkmember
30 = 0 and exists (select top 1 1 from QBMRelation dcr where dcr.UID_childcolumn = c.UID_DialogColumn ) ) as x  select @UID_TaskFollow = 'QBM-K-CommonPrepropColumnFK'
31 exec QBM_PDBQueueInsert_Bulk @UID_TaskFollow, @DBQueueElements_03  declare @DBQueueElements_04 QBM_YDBQueueRaw insert into @DBQueueElements_04 (object
32, subobject, genprocid) select x.uid, null, @GenProcID from (select distinct o.UID_DialogObject as UID, x.GenProcID from DialogObject o join DialogTable
33 t on o.UID_DialogTable = t.UID_DialogTable join @PrePropHelper x on t.TableName = x.uid_parameter and x.IsDeactivated <>x.IsDeactivatedOld ) as x exec
34 QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropObject', @DBQueueElements_04   end   END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 
3518, 1) WITH NOWAIT END CATCH  endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R return end 
36