Back to OIM Explorer

dbo.QBM_PMNTableDelete

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. References QBM_PJobCreate*

Source: sandbox-db sys.sql_modules

Source size: 5.765 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

References QBM_PJobCreate*

Typed Edges

references source dbo.QBM_FCVElementToObjectKey2 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_HODelete source text reference
references source dbo.QBM_PJobCreate_HODelete_L source text reference
references source dbo.QBM_PSessionContextSet source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

1CREATE PROCEDURE QBM_PMNTableDelete(2  @TargetTable varchar(30),3  @ElementColumn varchar(30),4  @AssignedElementColumn varchar(30),5  @TargetIsView BIT = 0,6  @FKTableNameElement varchar(30) = '',7  @FKColumnNameElement varchar(30) = ''8)9AS10BEGIN11  DECLARE @GenProcID varchar(38)12  DECLARE @SQLCmdDelete nvarchar(max) = N ''13  DECLARE @SQLCmdFire nvarchar(max) = N ''14  DECLARE @SQLAction nvarchar(max)15  DECLARE @TargetTableView varchar(30)16  DECLARE @IsAssignmentWithEvent BIT17  DECLARE @DebugSwitch int = 018  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')19  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')20  DECLARE @ElementCount int21  DECLARE @ElementIndex int22  DECLARE @DifferentTables QBM_YCursorBuffer23  DECLARE @ElementCountDifferentTables int24  DECLARE @ElementIndexDifferentTables int25  DECLARE @ElementLastDifferentTables int26  SET XACT_ABORT OFF27  BEGIN TRY28    SELECT29      @SQLCmdDelete = CONCAT('30	  delete ',31      @TargetTable,32      '33		from ',34      @TargetTable,35      ' z , #QBMDeltaDelete  d 36		where d.GenProcID = ''@GenProciD''37		 and z.',38      @ElementColumn,39      ' = d.element40		 and z.',41      @AssignedElementColumn,42      ' = d.AssignedElement43		')44    IF @TargetIsView = 045    BEGIN46      SELECT47        @SQLCmdFire = '48		declare @EntriesToFire QBM_YParameterList 49		declare @AddonElements QBM_YParameterList 50			51		insert into @EntriesToFire(Parameter1)52			select @XObjectKey@53			from #QBMDeltaDelete d54			where d.GenProcID = ''@GenProciD''55				'56      IF @FKTableNameElement > ' ' AND @FKColumnNameElement > ' '57      BEGIN58        SELECT59          @SQLCmdFire =concat(@SQLCmdFire,60          '61				62			insert into @AddonElements(Parameter1, Parameter2)63				select distinct x.XObjectKey, @XObjectKey@64				from #QBMDeltaDelete d join '65          ,66          @FKTableNameElement,67          ' x on x.',68          @FKColumnNameElement,69          ' = d.Element70				where d.GenProcID = ''@GenProciD''71			')72      END73      SELECT74        @SQLCmdFire = CONCAT(@SQLCmdFire,75        '		7677		exec QBM_PJobCreate_HODelete_L ''',78        @TargetTable,79        ''', @EntriesToFire, ''@GenProcID''80						, @AddonElements81						, @isToFreezeOnError = 18283				')84    END85    ELSE86    BEGIN87      SELECT88        @SQLCmdFire = '89		declare @EntriesToFire QBM_YParameterList 90		declare @AddonElements QBM_YParameterList 91			92		insert into @EntriesToFire(Parameter1)93			select @XObjectKey@94			from #QBMDeltaDelete d95			where d.GenProcID = ''@GenProciD''96			 and d.TargetTableView = ''@TargetTableView@''97				'98      IF @FKTableNameElement > ' ' AND @FKColumnNameElement > ' '99      BEGIN100        SELECT101          @SQLCmdFire = CONCAT(@SQLCmdFire,102          '103				104			insert into @AddonElements(Parameter1, Parameter2)105				select distinct x.XObjectKey, @XObjectKey@106				from #QBMDeltaDelete d join '107          ,108          @FKTableNameElement,109          ' x on x.',110          @FKColumnNameElement,111          ' = d.Element112				where d.GenProcID = ''@GenProciD''113			')114      END115      SELECT116        @SQLCmdFire = CONCAT(@SQLCmdFire,117        '		118119		exec QBM_PJobCreate_HODelete_L ''@TargetTableView@'', @EntriesToFire, ''@GenProcID''120						, @AddonElements121						, @isToFreezeOnError = 1122				'123        )124    END125    IF @DebugSwitch > 0126    BEGIN127      print @sqlcmdFire print @sqlcmdDelete128    END129    DECLARE @GenProcIDs_Delete QBM_YCursorBuffer130    INSERT INTO @GenProcIDs_Delete(UID1)131    SELECT132      DISTINCT d.GenProcID133    FROM #QBMDeltaDelete d134    SELECT @ElementCount = @@ROWCOUNT135    SELECT @ElementIndex = 1136    WHILE @ElementIndex <= @ElementCount137    BEGIN138      SELECT TOP 1 @GenProcID = bu.UID1139      FROM @GenProcIDs_Delete bu140      WHERE141        bu.ElementIndex = @ElementIndex142      EXEC QBM_PSessionContextSet 'GenProcID',143        @GenProcID144      IF @TargetIsView = 0145      BEGIN146        SELECT TOP 1 @IsAssignmentWithEvent = t.isAssignmentWithEvent147        FROM DialogTable t148          WITH(readpast)149        WHERE150          t.TableName = @TargetTable151        IF @IsAssignmentWithEvent = 0152        BEGIN153          SELECT154            @SQLAction = REPLACE(@SQLCmdDelete,155            '@GenProcID',156            @GenProcID)157        END158        ELSE159        BEGIN160          SELECT161            @SQLAction = replace(REPLACE(@SQLCmdFire, '@GenProcID', @GenProcID),162            '@XObjectKey@',163            CONCAT('dbo.QBM_FCVElementToObjectKey2( ''', @TargetTable, ''' , ''', @ElementColumn, ''' , d.Element, ''',164            @AssignedElementColumn, ''',  d.AssignedElement)'))165        END166        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLAction,167          @LockTimeout_ms = DEFAULT,168          @MaxWaitTimeForLock_s = DEFAULT,169          @ProcIDForJournal = DEFAULT,170          @HandleErrorSilent = 0171      END172      ELSE173      BEGIN174        DELETE @DifferentTables175        INSERT INTO @DifferentTables(UID1,176        Bit1)177        SELECT178          DISTINCT d.TargetTableView,179          t.isAssignmentWithEvent180        FROM #QBMDeltaDelete d181        JOIN DialogTable t182          WITH(readpast)183          ON d.TargetTableView = t.TableName184        SELECT @ElementCountDifferentTables = @@ROWCOUNT185        SELECT @ElementIndexDifferentTables = @@IDENTITY - @ElementCount +1186        SELECT @ElementLastDifferentTables = @@IDENTITY187        WHILE @ElementIndexDifferentTables <= @ElementLastDifferentTables188        BEGIN189          SELECT190            TOP 1 @TargetTableView = bu.UID1,191            @isAssignmentWithEvent = bu.Bit1192          FROM @DifferentTables bu193          WHERE194            bu.ElementIndex = @ElementIndexDifferentTables195          IF @isAssignmentWithEvent = 1196          BEGIN197            SELECT198              @SQLAction = replace(replace(REPLACE(@SQLCmdFire, '@GenProcID', @GenProcID), '@TargetTableView@',199              @TargetTableView),200              '@XObjectKey@',201              'dbo.QBM_FCVElementToObjectKey2( d.TargetTableView , d.ElementColumnView , d.Element, d.AssignedElementColumnView,  d.AssignedElement)')202          END203          ELSE204          BEGIN205            SELECT206              @SQLAction = REPLACE(@SQLCmdDelete,207              '@GenProcID',208              @GenProcID)209          END210          EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLAction,211            @LockTimeout_ms = DEFAULT,212            @MaxWaitTimeForLock_s = DEFAULT,213            @ProcIDForJournal = DEFAULT,214            @HandleErrorSilent = 0215          SELECT @ElementIndexDifferentTables += 1216        END217      END218      SELECT @ElementIndex += 1219    END220  END TRY221  BEGIN CATCH222    EXEC QBM_PSessionErrorAdd DEFAULT223    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()224    RAISERROR(@Rethrow,225    18,226    1)227      WITH NOWAIT228  END CATCH229  endLabel:230  EXEC QBM_PSessionContextSet 'GenProcID',231    @GenProcID_R232  EXEC QBM_PSessionContextSet 'XUser',233    @XUser_R234  RETURN235END

Open raw exported source

1     create   procedure QBM_PMNTableDelete (@TargetTable varchar(30)  , @ElementColumn varchar(30) , @AssignedElementColumn varchar(30) , @TargetIsView2 bit = 0   , @FKTableNameElement varchar(30) = ''  , @FKColumnNameElement varchar(30) = ''      ) as begin declare @GenProcID varchar(38) declare @SQLCmdDelete3 nvarchar(max) = N'' declare @SQLCmdFire nvarchar(max) = N'' declare @SQLAction nvarchar(max)  declare @TargetTableView varchar(30)  declare @IsAssignmentWithEvent4 bit declare @DebugSwitch int = 0 declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext5('XUser') declare @ElementCount int declare @ElementIndex int declare @DifferentTables QBM_YCursorBuffer declare @ElementCountDifferentTables int declare6 @ElementIndexDifferentTables int declare @ElementLastDifferentTables int SET XACT_ABORT OFF BEGIN TRY select @SQLCmdDelete = concat('7	  delete ' , @TargetTable8 , '9		from ' , @TargetTable , ' z , #QBMDeltaDelete  d 10		where d.GenProcID = ''@GenProciD''11		 and z.' , @ElementColumn , ' = d.element12		 and z.'13 , @AssignedElementColumn , ' = d.AssignedElement14		' ) 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 d21			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.Element29				where d.GenProcID = ''@GenProciD''30			' ) end  select @SQLCmdFire = concat31(@SQLCmdFire , '		3233		exec QBM_PJobCreate_HODelete_L ''' , @TargetTable , ''', @EntriesToFire, ''@GenProcID''34						, @AddonElements35						, @isToFreezeOnError = 13637				'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 d45			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.Element54				where d.GenProcID = ''@GenProciD''55			' ) end  select @SQLCmdFire = concat56(@SQLCmdFire , '		5758		exec QBM_PJobCreate_HODelete_L ''@TargetTableView@'', @EntriesToFire, ''@GenProcID''59						, @AddonElements60						, @isToFreezeOnError = 161				'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 <= @ElementCount64 begin select top 1 @GenProcID = bu.UID1 from @GenProcIDs_Delete bu where bu.ElementIndex = @ElementIndex  exec QBM_PSessionContextSet 'GenProcID', @GenProcID65 if @TargetIsView = 0 begin select top 1 @IsAssignmentWithEvent = t.isAssignmentWithEvent from DialogTable t with (readpast) where t.TableName = @TargetTable66 if @IsAssignmentWithEvent = 0 begin select @SQLAction = REPLACE(@SQLCmdDelete , '@GenProcID', @GenProcID) end else begin select @SQLAction = replace(REPLACE67(@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 = default69 , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = default , @HandleErrorSilent = 0 end else begin  delete  @DifferentTables  insert into @DifferentTables70 (UID1, Bit1) select distinct d.TargetTableView, t.isAssignmentWithEvent from #QBMDeltaDelete d join DialogTable t with (readpast) on d.TargetTableView71 = t.TableName select @ElementCountDifferentTables = @@ROWCOUNT select @ElementIndexDifferentTables = @@IDENTITY - @ElementCount +1 select @ElementLastDifferentTables72 = @@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 = replace74(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 = @SQLAction76 , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = default , @HandleErrorSilent = 0 select @ElementIndexDifferentTables77 += 1 end  end  select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow78() 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