Back to OIM Explorer

dbo.QBM_PBufferT_ProcessAll

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.379 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_FCVDatetimeToString source text reference
references source dbo.QBM_FGISessionErrorRethrow source text reference
references source dbo.QBM_PBufferT_ProcessTable source text reference
references source dbo.QBM_PJournal source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

dbo.QBM_PBufferT_ProcessAll_Delta

Complete Source

1CREATE PROCEDURE QBM_PBufferT_ProcessAll(2  @ModuleName varchar(3),3  @PurgeMode BIT = 04)5AS6BEGIN7  DECLARE @TableName varchar(30)8  DECLARE @tablesToProcess QBM_YCursorBuffer9  DECLARE @DebugLevel char(1) = 'W'10  DECLARE @DebugSwitch int = 011  DECLARE @msg nvarchar(2000)12  DECLARE @ElementBuffer QBM_YCursorBuffer13  DECLARE @ElementCount int14  DECLARE @ElementIndex int15  SET XACT_ABORT OFF16  BEGIN TRY17    INSERT INTO @tablesToProcess(Ident1)18    SELECT y.TableName19    FROM(20    SELECT21      DISTINCT bu.TableName22    FROM QBMBufferTransfer bu23    WHERE24      modulename = @ModuleName) AS y25    JOIN(26    SELECT27      TableName,28      SortOrder29    FROM QBM_VStartupTables30    UNION all31    SELECT32      '%',33      1000) AS x34      ON (y.TableName = x.TableName OR(y.TableName NOT IN(35    SELECT TableName36    FROM QBM_VStartupTables) AND x.TableName = '%'))37    JOIN sys.tables te38      WITH(readpast)39      ON te.name = y.TableName collate database_default40    ORDER BY x.SortOrder41    IF @PurgeMode = 142    BEGIN43      IF EXISTS(44        SELECT TOP 1 145        FROM @tablesToProcess t46        JOIN QBM_VStartupTables s47          ON t.Ident1 = s.TableName) AND EXISTS(48      SELECT TOP 1 149      FROM QBM_VStartupTables s50      LEFT51      OUTER52      JOIN @tablesToProcess t53        ON t.Ident1 = s.TableName54      WHERE55        t.Ident1 IS NULL)56      INSERT INTO @tablesToProcess(Ident1)57      SELECT s.TableName58      FROM QBM_VStartupTables s59      LEFT60      OUTER61      JOIN @tablesToProcess t62        ON t.Ident1 = s.TableName63      WHERE64        t.Ident1 IS NULL65    END66    INSERT INTO @ElementBuffer(Ident1)67    SELECT p.Ident168    FROM @tablesToProcess p69    ORDER BY p.ElementIndex70    SELECT @ElementCount = @@ROWCOUNT71    SELECT @ElementIndex = 172    WHILE @ElementIndex <= @ElementCount73    BEGIN74      SELECT TOP 1 @TableName = bu.Ident175      FROM @ElementBuffer bu76      WHERE77        bu.ElementIndex = @ElementIndex78      IF @DebugSwitch > 079      BEGIN80        print '----------------------------------------------------------' print '-- processing table ' + @TableName + dbo.QBM_FCVDatetimeToString(GETUTCDATE81        ()) print '----------------------------------------------------------'82        SELECT83          @msg = 'Table: ' + @TableName + ' QBM_PBufferT_ProcessAll ' + @ModuleName + STR(@PurgeMode)84        EXEC QBM_PJournal @msg,85          @@procid,86        'D',87          @DebugLevel88      END89      EXEC QBM_PBufferT_ProcessTable @ModuleName,90        @TableName,91        @PurgeMode92      SELECT @ElementIndex += 193    END94  END TRY95  BEGIN CATCH96    EXEC QBM_PSessionErrorAdd DEFAULT97    EXEC QBM_PJournal 'SessionError',98      @@procid,99    'E',100      @DebugLevel101    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()102    RAISERROR(@Rethrow,103    18,104    1)105      WITH NOWAIT106  END CATCH107END

Open raw exported source

1     create   procedure QBM_PBufferT_ProcessAll (@ModuleName varchar(3) , @PurgeMode bit = 0 ) as begin declare @TableName varchar(30)   declare2 @tablesToProcess QBM_YCursorBuffer    declare @DebugLevel char(1) = 'W' declare @DebugSwitch int = 0 declare @msg nvarchar(2000) declare @ElementBuffer3 QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY   insert into @tablesToProcess (Ident1) select y.TableName4 from (select distinct bu.TableName from QBMBufferTransfer bu where modulename = @ModuleName) as y join (select TableName, SortOrder from QBM_VStartupTables5 union all select '%', 1000 ) as x on (y.TableName = x.TableName or ( y.TableName not in (select TableName from QBM_VStartupTables) and x.TableName = '%'6 ) )  join sys.tables te with (readpast) on te.name = y.TableName collate database_default  order by x.SortOrder if @PurgeMode = 1 begin if exists (select7 top 1 1 from @tablesToProcess t join QBM_VStartupTables s on t.Ident1 = s.TableName ) and exists (select top 1 1 from QBM_VStartupTables s left outer 8join @tablesToProcess t on t.Ident1 = s.TableName where t.Ident1 is null ) insert into @tablesToProcess (Ident1) select s.TableName from QBM_VStartupTables9 s left outer join @tablesToProcess t on t.Ident1 = s.TableName where t.Ident1 is null end insert into @ElementBuffer(Ident1) select p.Ident1 from @tablesToProcess10 p order by p.ElementIndex select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @TableName11 = bu.Ident1 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print '----------------------------------------------------------'12 print '-- processing table ' + @TableName + dbo.QBM_FCVDatetimeToString(GETUTCDATE()) print '----------------------------------------------------------'13 select @msg = 'Table: ' + @TableName + ' QBM_PBufferT_ProcessAll ' + @ModuleName + STR(@PurgeMode) exec QBM_PJournal @msg, @@procid, 'D', @DebugLevel 14end  exec QBM_PBufferT_ProcessTable @ModuleName, @TableName , @PurgeMode select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default15 exec QBM_PJournal 'SessionError', @@procid, 'E', @DebugLevel declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 1816, 1) WITH NOWAIT END CATCH end 17