Back to OIM Explorer

dbo.QBM_PBufferT_ProcAll_Delta_i

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.483 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PBufferT_ProcessInsert source text reference
  • references source dbo.QBM_PBufferT_ProcessUpdate source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL219 lines
1CREATE PROCEDURE QBM_PBufferT_ProcAll_Delta_i(2  @ModuleName varchar(3),3  @TablePattern varchar(64)4)5AS6BEGIN7  DECLARE @TableName varchar(30)8  DECLARE @OperationType varchar(1)9  DECLARE @ColumnName varchar(30)10  DECLARE @SQLCmd nvarchar(max)11  DECLARE @DebugSwitch int = 012  DECLARE @DebugLevel char(1) = 'W'13  DECLARE @ComlumNames QBM_YParameterList14  DECLARE @Message nvarchar(1024)15  DECLARE @Anzahl int16  DECLARE @ElementBuffer QBM_YCursorBuffer17  DECLARE @ElementCount int18  DECLARE @ElementIndex int19  SET XACT_ABORT OFF20  BEGIN TRY21    IF @debugSwitch > 022    BEGIN23      SELECT24        @message = CONCAT('QBM_PBufferT_ProcAll_Delta_i ',25        ' module ',26        @ModuleName)27      EXEC QBM_PJournal @Message,28        @@procid,29      'D',30        @DebugLevel31    END32    INSERT INTO @ElementBuffer(UID1,33    UID2)34    SELECT35      DISTINCT t.OperationType,36      t.TableName37    FROM QBMBufferTransfer t38    JOIN sys.objects o39      WITH(readpast)40      ON t.TableName = o.name41    WHERE42      t.TableName LIKE @TablePattern43    ORDER BY 1,44    245    SELECT @ElementCount = @@ROWCOUNT46    SELECT @ElementIndex = 147    WHILE @ElementIndex <= @ElementCount48    BEGIN49      SELECT50        TOP 1 @OperationType = bu.UID1,51        @TableName = bu.UID252      FROM @ElementBuffer bu53      WHERE54        bu.ElementIndex = @ElementIndex55      IF @OperationType = 'D'56      BEGIN57        SELECT58          @SQLCmd = CONCAT('delete ',59          @TableName,60          '61									 where XObjectKey in (select t.ObjectKeyOfRow62															from QBMBufferTransfer t63															where t.OperationType = ''D''64															 and t.TableName = '''65          ,66          @TableName,67          '''68														)')69        EXEC @Anzahl = QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLCmd,70          @LockTimeout_ms = DEFAULT,71          @MaxWaitTimeForLock_s = 4.0,72          @ProcIDForJournal = DEFAULT,73          @HandleErrorSilent = 074        IF @debugSwitch > 075        BEGIN76          SELECT @Message = str(@Anzahl)77          SELECT78            @message = CONCAT(@Message,79            ' rows deleted for Table ',80            @TableName,81            ' module ',82            @ModuleName)83          EXEC QBM_PJournal @Message,84            @@procid,85          'D',86            @DebugLevel87          SELECT TOP 1 @Anzahl = count(*)88          FROM QBMBufferTransfer t89          WHERE90            t.OperationType = 'D' AND t.TableName = @TableName91          SELECT92            @message = CONCAT(str(@anzahl),93            ' rows to deleted for Table ',94            @TableName,95            ' module ',96            @ModuleName)97          EXEC QBM_PJournal @Message,98            @@procid,99          'D',100            @DebugLevel101        END102      END103      IF @OperationType = 'I'104      BEGIN105        truncate TABLE #QBMBufferTransferOneTable106        INSERT INTO #QBMBufferTransferOneTable(ObjectKeyOfRow,107        ColumnName,108        ContentShort,109        HasContentFull,110        ContentFull)111        SELECT112          bu.ObjectKeyOfRow,113          bu.ColumnName,114          bu.ContentShort,115          bu.HasContentFull,116          bu.ContentFull117        FROM QBMBufferTransfer bu118        JOIN sys.objects o119          WITH(readpast)120          ON bu.TableName = o.name121        JOIN sys.columns c122          WITH(readpast)123          ON bu.ColumnName = c.name AND c.object_id = o.object_id124        WHERE125          bu.OperationType = 'I' AND bu.TableName = @TableName126        SELECT @Message = str(@@ROWCOUNT)127        IF @debugSwitch > 0128        BEGIN129          SELECT130            @message = CONCAT(@Message,131            ' rows insertd for Table ',132            @TableName,133            ' module ',134            @ModuleName)135          EXEC QBM_PJournal @Message,136            @@procid,137          'D',138            @DebugLevel139        END140        EXEC QBM_PBufferT_ProcessInsert @modulename,141          @TableName142      END143      IF @OperationType = 'U'144      BEGIN145        DELETE @ComlumNames146        INSERT INTO @ComlumNames(Parameter1)147        SELECT148          DISTINCT t.ColumnName149        FROM QBMBufferTransfer t150        JOIN sys.objects o151          WITH(readpast)152          ON t.TableName = o.name153        JOIN sys.columns c154          WITH(readpast)155          ON t.ColumnName = c.name AND c.object_id = o.object_id156        WHERE157          t.OperationType = 'U' AND t.TableName = @TableName158        SELECT @ColumnName = '#'159        WHILE @ColumnName > ' '160        BEGIN161          SELECT @ColumnName = NULL162          SELECT TOP 1 @ColumnName = cn.Parameter1163          FROM @ComlumNames cn164          IF @ColumnName IS NULL165          BEGIN166            CONTINUE167          END168          truncate TABLE #QBMBufferTransferOneTable169          INSERT INTO #QBMBufferTransferOneTable(ObjectKeyOfRow,170          ColumnName,171          ContentShort,172          HasContentFull,173          ContentFull)174          SELECT175            t.ObjectKeyOfRow,176            t.ColumnName,177            t.ContentShort,178            t.HasContentFull,179            t.ContentFull180          FROM QBMBufferTransfer t181          WHERE182            t.OperationType = 'U' AND t.TableName = @TableName AND t.ColumnName = @ColumnName183          SELECT @Message = str(@@ROWCOUNT)184          IF @debugSwitch > 0185          BEGIN186            SELECT187              @message = CONCAT(@Message,188              ' rows updated for Column ',189              @columnname,190              ' Table ',191              @TableName,192              ' module ',193              @ModuleName)194            EXEC QBM_PJournal @Message,195              @@procid,196            'D',197              @DebugLevel198          END199          EXEC QBM_PBufferT_ProcessUpdate @modulename,200            @TableName,201            @ColumnName202          DELETE @ComlumNames203          FROM @ComlumNames cn204          WHERE205            cn.Parameter1 = @ColumnName206        END207      END208      SELECT @ElementIndex += 1209    END210  END TRY211  BEGIN CATCH212    EXEC QBM_PSessionErrorAdd DEFAULT213    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()214    RAISERROR(@Rethrow,215    18,216    1)217      WITH NOWAIT218  END CATCH219END
Open raw exported source
SQL ยท Raw34 lines
1   create   procedure QBM_PBufferT_ProcAll_Delta_i (@ModuleName varchar(3) , @TablePattern varchar(64) ) as begin declare @TableName varchar(30)2 declare @OperationType varchar(1) declare @ColumnName varchar(30) declare @SQLCmd nvarchar(max) declare @DebugSwitch int = 0 declare @DebugLevel char(13) = 'W' declare @ComlumNames QBM_YParameterList declare @Message nvarchar(1024) declare @Anzahl int declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount4 int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY if @debugSwitch > 0 begin select @message = concat('QBM_PBufferT_ProcAll_Delta_i ' , ' module '5, @ModuleName ) exec QBM_PJournal @Message, @@procid, 'D', @DebugLevel  end insert into @ElementBuffer(UID1, UID2) select distinct t.OperationType, t.TableName6 from QBMBufferTransfer t join sys.objects o with (readpast) on t.TableName = o.name where t.TableName like @TablePattern order by 1,2 select @ElementCount7 = @@ROWCOUNT select @ElementIndex = 1       while @ElementIndex <= @ElementCount begin select top 1 @OperationType = bu.UID1 , @TableName = bu.UID2 from8 @ElementBuffer bu where bu.ElementIndex = @ElementIndex if @OperationType = 'D' begin select @SQLCmd = concat ('delete ', @TableName , '9									 where XObjectKey in (select t.ObjectKeyOfRow10															from QBMBufferTransfer t11															where t.OperationType = ''D''12															 and t.TableName = '''13, @TableName , '''14														)' ) exec @Anzahl = QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLCmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s15 = 4.0 , @ProcIDForJournal = default , @HandleErrorSilent = 0 if @debugSwitch > 0 begin select @Message = str(@Anzahl) select @message = concat(@Message16 , ' rows deleted for Table ' , @TableName , ' module ', @ModuleName ) exec QBM_PJournal @Message, @@procid, 'D', @DebugLevel  select top 1 @Anzahl = count17(*) from QBMBufferTransfer t where t.OperationType = 'D' and t.TableName = @TableName select @message = concat(str(@anzahl) , ' rows to deleted for Table '18 , @TableName , ' module ', @ModuleName ) exec QBM_PJournal @Message, @@procid, 'D', @DebugLevel  end end  if @OperationType = 'I' begin truncate table19 #QBMBufferTransferOneTable insert into #QBMBufferTransferOneTable(ObjectKeyOfRow, ColumnName, ContentShort, HasContentFull, ContentFull ) select bu.ObjectKeyOfRow20, bu.ColumnName, bu.ContentShort, bu.HasContentFull, bu.ContentFull from QBMBufferTransfer bu join sys.objects o with (readpast) on bu.TableName = o.name21 join sys.columns c with (readpast) on bu.ColumnName = c.name and c.object_id = o.object_id where bu.OperationType = 'I' and bu.TableName = @TableName 22select @Message = str(@@ROWCOUNT) if @debugSwitch > 0 begin select @message = concat(@Message , ' rows insertd for Table ' , @TableName , ' module ', @ModuleName23 ) exec QBM_PJournal @Message, @@procid, 'D', @DebugLevel   end exec QBM_PBufferT_ProcessInsert @modulename, @TableName end  if @OperationType = 'U' begin24 delete  @ComlumNames  insert into @ComlumNames(Parameter1) select distinct t.ColumnName from QBMBufferTransfer t join sys.objects o with (readpast) on25 t.TableName = o.name join sys.columns c with (readpast) on t.ColumnName = c.name and c.object_id = o.object_id where t.OperationType = 'U' and t.TableName26 = @TableName      select @ColumnName = '#' while @ColumnName > ' ' begin select @ColumnName = null select top 1 @ColumnName = cn.Parameter1 from @ComlumNames27 cn if @ColumnName is null begin continue end truncate table #QBMBufferTransferOneTable insert into #QBMBufferTransferOneTable(ObjectKeyOfRow, ColumnName28, ContentShort, HasContentFull, ContentFull ) select t.ObjectKeyOfRow, t.ColumnName, t.ContentShort, t.HasContentFull, t.ContentFull from QBMBufferTransfer29 t where t.OperationType = 'U' and t.TableName = @TableName and t.ColumnName = @ColumnName select @Message = str(@@ROWCOUNT) if @debugSwitch > 0 begin 30select @message = concat(@Message , ' rows updated for Column ' , @columnname , ' Table ' , @TableName , ' module ', @ModuleName ) exec QBM_PJournal @Message31, @@procid, 'D', @DebugLevel   end exec QBM_PBufferT_ProcessUpdate @modulename, @TableName, @ColumnName delete  @ComlumNames from @ComlumNames cn where32 cn.Parameter1 = @ColumnName end  end  select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(100033) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH end 34