Back to OIM Explorer

dbo.QBM_PMNTableInsertOwnPK

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. HOFireEvent -> ' , @TargetTable , '., concat( at line 53; References QBM_PJobCreate*; References QBM_PJobCreate_HOFireEvent*

Source: sandbox-db sys.sql_modules

Source size: 6.384 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • Object-layer bridge detected through QBM_PJobCreate helper usage.

Relations

  • HOFireEvent -> ' , @TargetTable , '., concat( at line 53
  • References QBM_PJobCreate*
  • References QBM_PJobCreate_HOFireEvent*

Typed Edges

  • references source dbo.QBM_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_FCVStringToGUID source text reference
  • references source dbo.QBM_FCVStringToGUIDMAll source text reference
  • references source dbo.QBM_FGIColumnExists source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PJobCreate source text reference
  • references source dbo.QBM_PJobCreate_HOFireEvent source text reference
  • references source dbo.QBM_PJobCreate_HOFireEvent_L source text reference
  • references source dbo.QBM_PMNTableInsert source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL272 lines
1CREATE PROCEDURE QBM_PMNTableInsertOwnPK(2  @TargetTable varchar(30),3  @ElementColumn varchar(30),4  @AssignedElementColumn varchar(30),5  @FKTableNameElement varchar(30) = '',6  @FKColumnNameElement varchar(30) = ''7)8AS9BEGIN10  DECLARE @GenProcID varchar(38)11  DECLARE @SQLCmdInsert nvarchar(max) = N ''12  DECLARE @SQLCmdFire nvarchar(max) = N ''13  DECLARE @SQLAction nvarchar(max)14  DECLARE @InsertLine nvarchar(max)15  DECLARE @SelectLine nvarchar(max)16  DECLARE @UID_DialogTableTarget varchar(38)17  DECLARE @IsAssignmentWithEvent BIT18  DECLARE @PKColumnName varchar(30)19  DECLARE @DebugSwitch int = 020  DECLARE @ExistsXObjectKey BIT21  DECLARE @ExistsXOrigin BIT22  DECLARE @ExistsXIsInEffect BIT23  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')24  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')25  DECLARE @ElementCount int26  DECLARE @ElementIndex int27  DECLARE @MaxWaitTimeForMainOperation float = 8.028  SET XACT_ABORT OFF29  BEGIN TRY30    SELECT TOP 1 @UID_DialogTableTarget = uid_dialogtable31    FROM DialogTable32    WHERE33      TableName = @TargetTable34    SELECT35      @ExistsXObjectKey = dbo.QBM_FGIColumnExists(@TargetTable,36      'XObjectKey')37    SELECT38      @ExistsXOrigin = dbo.QBM_FGIColumnExists(@TargetTable,39      'XOrigin')40    SELECT41      @ExistsXIsInEffect = dbo.QBM_FGIColumnExists(@TargetTable,42      'XIsInEffect')43    SELECT TOP 1 @PKColumnName = c.ColumnName44    FROM DialogColumn c45    WHERE46      UID_DialogTable = @UID_DialogTableTarget AND IsPKMember = 147    UPDATE #QBMDeltaInsert48    SET NewPK = dbo.QBM_FCVStringToGUIDMAll(NULL,49    Element,50    AssignedElement)51    WHERE52      ISNULL(NewPK,53    '') = ''54    SELECT55      @InsertLine = CONCAT('56		insert into ',57      @TargetTable,58      ' ( ',59      @PKColumnName,60      ', ',61      @ElementColumn,62      ' , ',63      @AssignedElementColumn)64    SELECT65      @SelectLine = '66		    select d.NewPK, d.Element, d.AssignedElement '67    IF @ExistsXObjectKey = 168    BEGIN69      SELECT70        @InsertLine = CONCAT(@InsertLine,71        ', XObjectKey')72      SELECT73        @SelectLine = CONCAT(@SelectLine,74        ', @XObjectKey@')75    END76    IF @ExistsXIsInEffect = 177    BEGIN78      SELECT79        @InsertLine = CONCAT(@InsertLine,80        ', XIsInEffect')81      SELECT82        @SelectLine = CONCAT(@SelectLine,83        ', d.XIsInEffect')84    END85    IF @ExistsXOrigin = 186    BEGIN87      SELECT88        @InsertLine = CONCAT(@InsertLine,89        ', XOrigin')90      SELECT91        @SelectLine = CONCAT(@SelectLine,92        ', d.XOrigin')93    END94    IF EXISTS(95      SELECT TOP 1 196      FROM DialogColumn c97      WHERE98        c.UID_DialogTable = @UID_DialogTableTarget AND c.ColumnName = 'XdateInserted')99    BEGIN100      SELECT101        @InsertLine = CONCAT(@InsertLine,102        ', XDateInserted , XDateUpdated ,  XUserInserted , XUserUpdated ')103      SELECT104        @SelectLine = CONCAT(@SelectLine,105        ', getutcdate() , getutcdate() ,  ''QBM_DBQueueProcess'' , ''QBM_DBQueueProcess'' ')106    END107    SELECT108      @SQLCmdInsert = CONCAT(@InsertLine,109      ')',110      @SelectLine,111      '									112		from #QBMDeltaInsert  d 113		where d.GenProcID = ''@GenProciD''114		 and Not exists (select top 1 1115							from ',116      @TargetTable,117      ' z118							where z.',119      @ElementColumn,120      ' = d.element121							 and z.',122      @AssignedElementColumn,123      ' = d.AssignedElement124						)')125    SELECT TOP 1 @IsAssignmentWithEvent = t.isAssignmentWithEvent126    FROM DialogTable t127    WHERE128      t.UID_DialogTable = @UID_DialogTableTarget129    IF @IsAssignmentWithEvent = 1 AND NOT EXISTS(130      SELECT TOP 1 1131      FROM QBMEvent q132      JOIN JobEventGen e133        ON q.UID_QBMEvent = e.UID_QBMEvent134      JOIN JobChain c135        ON e.UID_JobChain = c.UID_JobChain136      WHERE137        q.UID_DialogTable = @UID_DialogTableTarget AND q.EventName = 'Insert' AND c.NoGenerate = 0 AND c.IsDeactivatedByPreProcessor = 0)138    BEGIN139      SELECT @IsAssignmentWithEvent = 0140    END141    IF @IsAssignmentWithEvent = 1142    BEGIN143      SELECT144        @SQLCmdFire = '145		declare @EntriesToFire QBM_YParameterList146		declare @AddonElements QBM_YParameterList  147			148		insert into @EntriesToFire(Parameter1)149			select @XObjectKey@150			from #QBMDeltaInsert d151			where d.GenProcID = ''@GenProciD''152				'153      IF @ExistsXIsInEffect = 1154      BEGIN155        SELECT156          @SQLCmdFire = CONCAT(@SQLCmdFire,157          '158			and d.XIsInEffect = 1159			')160      END161      IF @FKTableNameElement > ' ' AND @FKColumnNameElement > ' '162      BEGIN163        SELECT164          @SQLCmdFire = CONCAT(@SQLCmdFire,165          '166				167			insert into @AddonElements(Parameter1, Parameter2)168				select distinct x.XObjectKey, @XObjectKey@169				from #QBMDeltaInsert d join '170          ,171          @FKTableNameElement,172          ' x on x.',173          @FKColumnNameElement,174          ' = d.Element175				where d.GenProcID = ''@GenProciD''176			')177      END178      SELECT179        @SQLCmdFire = CONCAT(@SQLCmdFire,180        '	181				182		exec QBM_PJobCreate_HOFireEvent_L ''',183        @TargetTable,184        ''', @EntriesToFire, ''Insert'', ''@GenProcID''185				 , @AddonElements186				, @isToFreezeOnError = 1187				188				')189    END190    SELECT191      @SQLCmdInsert = REPLACE(@SQLCmdInsert,192      '@XObjectKey@',193      CONCAT('dbo.QBM_FCVElementToObjectKey1(''', @TargetTable, ''', ''', @PKColumnName, ''',  d.NewPK )'))194    SELECT195      @SQLCmdFire = REPLACE(@SQLCmdFire,196      '@XObjectKey@',197      CONCAT('dbo.QBM_FCVElementToObjectKey1(''', @TargetTable, ''', ''', @PKColumnName, ''',  d.NewPK )'))198    IF @DebugSwitch > 0199    BEGIN200      print @SQLCmdInsert print @SQLCmdFire201    END202    DECLARE @GenProcIDs_CountInsert QBM_YCursorBuffer203    INSERT INTO @GenProcIDs_CountInsert(UID1)204    SELECT205      DISTINCT d.GenProcID206    FROM #QBMDeltaInsert d207    SELECT @ElementCount = @@ROWCOUNT208    SELECT @ElementIndex = 1209    WHILE @ElementIndex <= @ElementCount210    BEGIN211      SELECT TOP 1 @GenProcID = bu.UID1212      FROM @GenProcIDs_CountInsert bu213      WHERE214        bu.ElementIndex = @ElementIndex215      EXEC QBM_PSessionContextSet 'GenProcID',216        @GenProcID217      SELECT218        @SQLAction = REPLACE(@SQLCmdInsert,219        '@GenProcID',220        @GenProcID)221      IF @DebugSwitch > 0222      BEGIN223        print @SQLAction224        BEGIN TRY225          EXEC sp_executesql @SQLAction226        END TRY227        BEGIN CATCH228          EXEC QBM_PSessionErrorAdd DEFAULT229          RAISERROR('',230          18,231          1)232            WITH NOWAIT233        END CATCH234      END235      ELSE236      BEGIN237        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLAction,238          @LockTimeout_ms = DEFAULT,239          @MaxWaitTimeForLock_s = @MaxWaitTimeForMainOperation,240          @ProcIDForJournal = DEFAULT,241          @HandleErrorSilent = 0242      END243      IF @SQLCmdFire > ' '244      BEGIN245        SELECT246          @SQLAction = REPLACE(@SQLCmdFire,247          '@GenProcID',248          @GenProcID)249        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLAction,250          @LockTimeout_ms = DEFAULT,251          @MaxWaitTimeForLock_s = 4.0,252          @ProcIDForJournal = DEFAULT,253          @HandleErrorSilent = 0254      END255      SELECT @ElementIndex += 1256    END257  END TRY258  BEGIN CATCH259    EXEC QBM_PSessionErrorAdd DEFAULT260    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()261    RAISERROR(@Rethrow,262    18,263    1)264      WITH NOWAIT265  END CATCH266  endLabel:267  EXEC QBM_PSessionContextSet 'GenProcID',268    @GenProcID_R269  EXEC QBM_PSessionContextSet 'XUser',270    @XUser_R271  RETURN272END
Open raw exported source
SQL ยท Raw71 lines
1   create   procedure QBM_PMNTableInsertOwnPK (@TargetTable varchar(30)  , @ElementColumn varchar(30) , @AssignedElementColumn varchar(30) , @FKTableNameElement2 varchar(30) = ''  , @FKColumnNameElement varchar(30) = ''      ) as begin declare @GenProcID varchar(38) declare @SQLCmdInsert nvarchar(max) = N'' declare3 @SQLCmdFire nvarchar(max) = N'' declare @SQLAction nvarchar(max) declare @InsertLine nvarchar(max) declare @SelectLine nvarchar(max)  declare @UID_DialogTableTarget4 varchar(38) declare @IsAssignmentWithEvent bit declare @PKColumnName varchar(30) declare @DebugSwitch int = 0 declare @ExistsXObjectKey bit declare @ExistsXOrigin5 bit declare @ExistsXIsInEffect bit declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext6('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(@TargetTable8, 'XObjectKey') select @ExistsXOrigin = dbo.QBM_FGIColumnExists(@TargetTable, 'XOrigin') select @ExistsXIsInEffect = dbo.QBM_FGIColumnExists(@TargetTable9, 'XIsInEffect') select top 1 @PKColumnName = c.ColumnName from DialogColumn c where UID_DialogTable = @UID_DialogTableTarget and IsPKMember = 1 update10 #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@') end15 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 if17 exists(select top 1 1 from DialogColumn c where c.UID_DialogTable = @UID_DialogTableTarget and c.ColumnName = 'XdateInserted' ) begin select @InsertLine18 = 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 123							from '24 , @TargetTable , ' z25							where z.' , @ElementColumn , ' = d.element26							 and z.' , @AssignedElementColumn , ' = d.AssignedElement27						)' ) 28select top 1 @IsAssignmentWithEvent = t.isAssignmentWithEvent from DialogTable t where t.UID_DialogTable = @UID_DialogTableTarget  if @IsAssignmentWithEvent29 = 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_JobChain30 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_YParameterList33		declare @AddonElements QBM_YParameterList  34			35		insert into @EntriesToFire(Parameter1)36			select @XObjectKey@37			from #QBMDeltaInsert d38			where d.GenProcID = ''@GenProciD''39				'40  if @ExistsXIsInEffect = 1 begin select @SQLCmdFire = concat(@SQLCmdFire , '41			and d.XIsInEffect = 142			' ) end if @FKTableNameElement > ' ' and @FKColumnNameElement43 > ' ' 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.Element49				where d.GenProcID = ''@GenProciD''50			' ) end  select @SQLCmdFire = concat51(@SQLCmdFire , '	52				53		exec QBM_PJobCreate_HOFireEvent_L ''' , @TargetTable , ''', @EntriesToFire, ''Insert'', ''@GenProcID''54				 , @AddonElements55				, @isToFreezeOnError = 156				57				'58 ) end select @SQLCmdInsert = REPLACE(@SQLCmdInsert, '@XObjectKey@', concat('dbo.QBM_FCVElementToObjectKey1(''' , @TargetTable , ''', ''' , @PKColumnName59 , ''',  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_YCursorBuffer61 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 exec63 QBM_PSessionContextSet 'GenProcID', @GenProcID select @SQLAction = REPLACE(@SQLCmdInsert , '@GenProcID', @GenProcID) if @DebugSwitch > 0 begin print @SQLAction64 BEGIN TRY exec sp_executesql @SQLAction END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end else begin65  exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLAction , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = @MaxWaitTimeForMainOperation , @ProcIDForJournal66 = default , @HandleErrorSilent = 0 end if @SQLCmdFire > ' ' begin select @SQLAction = REPLACE(@SQLCmdFire , '@GenProcID', @GenProcID) exec QBM_PExecuteSQLWithRetry_LLP67 @SQLStatement = @SQLAction , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = 4.0 , @ProcIDForJournal = default , @HandleErrorSilent = 0 end select68 @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR69 (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R 70return end 71