dbo.QBM_PMNTableInsertOwnPK

1   create   procedure QBM_PMNTableInsertOwnPK (@TargetTable varchar(30)  , @ElementColumn varchar(30) , @AssignedElementColumn varchar(30) , @FKTableNameElement
2 varchar(30) = ''  , @FKColumnNameElement varchar(30) = ''      ) as begin declare @GenProcID varchar(38) declare @SQLCmdInsert nvarchar(max) = N'' declare
3 @SQLCmdFire nvarchar(max) = N'' declare @SQLAction nvarchar(max) declare @InsertLine nvarchar(max) declare @SelectLine nvarchar(max)  declare @UID_DialogTableTarget
4 varchar(38) declare @IsAssignmentWithEvent bit declare @PKColumnName varchar(30) declare @DebugSwitch int = 0 declare @ExistsXObjectKey bit declare @ExistsXOrigin
5 bit declare @ExistsXIsInEffect bit declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext
6('XUser') declare @ElementCount int declare @ElementIndex int declare @MaxWaitTimeForMainOperation float = 8.0 SET XACT_ABORT OFF BEGIN TRY select top 
71 @UID_DialogTableTarget = uid_dialogtable from DialogTable where TableName = @TargetTable select @ExistsXObjectKey = dbo.QBM_FGIColumnExists(@TargetTable
8, 'XObjectKey') select @ExistsXOrigin = dbo.QBM_FGIColumnExists(@TargetTable, 'XOrigin') select @ExistsXIsInEffect = dbo.QBM_FGIColumnExists(@TargetTable
9, 'XIsInEffect') select top 1 @PKColumnName = c.ColumnName from DialogColumn c where UID_DialogTable = @UID_DialogTableTarget and IsPKMember = 1 update
10 #QBMDeltaInsert set NewPK = dbo.QBM_FCVStringToGUIDMAll(null, Element, AssignedElement)  where ISNULL(NewPK, '') = ''  select @InsertLine = concat('
11		insert into '
12 , @TargetTable , ' ( ', @PKColumnName , ', ' , @ElementColumn , ' , ' , @AssignedElementColumn ) select @SelectLine = '
13		    select d.NewPK, d.Element, d.AssignedElement '
14 if @ExistsXObjectKey = 1 begin select @InsertLine = concat(@InsertLine , ', XObjectKey') select @SelectLine = concat(@SelectLine , ', @XObjectKey@') end
15 if @ExistsXIsInEffect = 1 begin select @InsertLine = concat(@InsertLine , ', XIsInEffect') select @SelectLine = concat(@SelectLine , ', d.XIsInEffect'
16) end if @ExistsXOrigin = 1 begin select @InsertLine = concat(@InsertLine , ', XOrigin') select @SelectLine = concat(@SelectLine , ', d.XOrigin') end if
17 exists(select top 1 1 from DialogColumn c where c.UID_DialogTable = @UID_DialogTableTarget and c.ColumnName = 'XdateInserted' ) begin select @InsertLine
18 = concat(@InsertLine , ', XDateInserted , XDateUpdated ,  XUserInserted , XUserUpdated ')  select @SelectLine = concat(@SelectLine, ', getutcdate() , getutcdate() ,  ''QBM_DBQueueProcess'' , ''QBM_DBQueueProcess'' '
19) end select @SQLCmdInsert = concat(@InsertLine , ')' , @SelectLine , '									
20		from #QBMDeltaInsert  d 
21		where d.GenProcID = ''@GenProciD''
22		 and Not exists (select top 1 1
23							from '
24 , @TargetTable , ' z
25							where z.' , @ElementColumn , ' = d.element
26							 and z.' , @AssignedElementColumn , ' = d.AssignedElement
27						)' ) 
28select top 1 @IsAssignmentWithEvent = t.isAssignmentWithEvent from DialogTable t where t.UID_DialogTable = @UID_DialogTableTarget  if @IsAssignmentWithEvent
29 = 1 and not exists (select top 1 1 from QBMEvent q join JobEventGen e on q.UID_QBMEvent = e.UID_QBMEvent join JobChain c on e.UID_JobChain = c.UID_JobChain
30 where q.UID_DialogTable = @UID_DialogTableTarget and q.EventName = 'Insert' and c.NoGenerate = 0 and c.IsDeactivatedByPreProcessor = 0 ) begin select 
31@IsAssignmentWithEvent = 0 end  if @IsAssignmentWithEvent = 1 begin select @SQLCmdFire = '
32		declare @EntriesToFire QBM_YParameterList
33		declare @AddonElements QBM_YParameterList  
34			
35		insert into @EntriesToFire(Parameter1)
36			select @XObjectKey@
37			from #QBMDeltaInsert d
38			where d.GenProcID = ''@GenProciD''
39				'
40  if @ExistsXIsInEffect = 1 begin select @SQLCmdFire = concat(@SQLCmdFire , '
41			and d.XIsInEffect = 1
42			' ) end if @FKTableNameElement > ' ' and @FKColumnNameElement
43 > ' ' begin select @SQLCmdFire = concat(@SQLCmdFire , '
44				
45			insert into @AddonElements(Parameter1, Parameter2)
46				select distinct x.XObjectKey, @XObjectKey@
47				from #QBMDeltaInsert d join '
48 , @FKTableNameElement , ' x on x.' , @FKColumnNameElement , ' = d.Element
49				where d.GenProcID = ''@GenProciD''
50			' ) end  select @SQLCmdFire = concat
51(@SQLCmdFire , '	
52				
53		exec QBM_PJobCreate_HOFireEvent_L ''' , @TargetTable , ''', @EntriesToFire, ''Insert'', ''@GenProcID''
54				 , @AddonElements
55				, @isToFreezeOnError = 1
56				
57				'
58 ) end select @SQLCmdInsert = REPLACE(@SQLCmdInsert, '@XObjectKey@', concat('dbo.QBM_FCVElementToObjectKey1(''' , @TargetTable , ''', ''' , @PKColumnName
59 , ''',  d.NewPK )') ) select @SQLCmdFire = REPLACE(@SQLCmdFire, '@XObjectKey@', concat('dbo.QBM_FCVElementToObjectKey1(''' , @TargetTable , ''', ''' ,
60 @PKColumnName , ''',  d.NewPK )') ) if @DebugSwitch > 0 begin print @SQLCmdInsert print @SQLCmdFire end declare @GenProcIDs_CountInsert QBM_YCursorBuffer
61 insert into @GenProcIDs_CountInsert (UID1) select distinct d.GenProcID from #QBMDeltaInsert d select @ElementCount = @@ROWCOUNT select @ElementIndex =
62 1 while @ElementIndex <= @ElementCount begin select top 1 @GenProcID = bu.UID1 from @GenProcIDs_CountInsert bu where bu.ElementIndex = @ElementIndex exec
63 QBM_PSessionContextSet 'GenProcID', @GenProcID select @SQLAction = REPLACE(@SQLCmdInsert , '@GenProcID', @GenProcID) if @DebugSwitch > 0 begin print @SQLAction
64 BEGIN TRY exec sp_executesql @SQLAction END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end else begin
65  exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLAction , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = @MaxWaitTimeForMainOperation , @ProcIDForJournal
66 = default , @HandleErrorSilent = 0 end if @SQLCmdFire > ' ' begin select @SQLAction = REPLACE(@SQLCmdFire , '@GenProcID', @GenProcID) exec QBM_PExecuteSQLWithRetry_LLP
67 @SQLStatement = @SQLAction , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = 4.0 , @ProcIDForJournal = default , @HandleErrorSilent = 0 end select
68 @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR
69 (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R 
70return end 
71