Back to OIM Explorer

dbo.QBM_PTableRevisionFill

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 1.331 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_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL54 lines
1CREATE PROCEDURE QBM_PTableRevisionFill2AS3BEGIN4  DECLARE @dbid int = DB_ID()5  SET XACT_ABORT OFF6  BEGIN TRY7    merge INTO QBMTableRevision AS t using(8    SELECT9      DISTINCT dt.TableName10    FROM QBM_VTablesForRevision dt11    WHERE12      dt.IsForQBMTableRevision = 1 AND dbo.QBM_FGIConfigparmValue('Common\TableRevision') > ' ') AS s13      ON t.TableName = s.TableName AND t.ServerProcess = 0 WHEN NOT matched BY source AND t.serverprocess = 0 THEN14    DELETE WHEN NOT matched BY target THEN15    INSERT(TableName)16    VALUES(s.Tablename);17    UPDATE QBMTableRevision18    SET ServerProcess = -119    FROM QBMTableRevision r20      WITH(readpast)21    LEFT22    OUTER23    JOIN sys.sysprocesses p24      ON r.ServerProcess = p.spid AND p.dbid = @dbid25    WHERE26      p.spid IS NULL AND r.ServerProcess > 027    UPDATE QBMTableRevision28    SET RevisionDate = a.maxRevisionDate29    FROM QBMTableRevision u30    JOIN(31    SELECT32      r.TableName,33      MAX(r.RevisionDate) AS maxRevisionDate34    FROM QBMTableRevision r35      WITH(readpast)36    WHERE37      r.ServerProcess <= 0 AND r.RevisionDate > ' '38    GROUP BY r.TableName) AS a39      ON u.TableName = a.TableName AND u.ServerProcess = 0 waitfor delay '00:00:02'40    DELETE QBMTableRevision41    WHERE42      ServerProcess = -143  END TRY44  BEGIN CATCH45    EXEC QBM_PSessionErrorAdd DEFAULT46    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()47    RAISERROR(@Rethrow,48    18,49    1)50      WITH NOWAIT51  END CATCH52  endLabel:53  RETURN54END
Open raw exported source
SQL ยท Raw10 lines
1   create   procedure QBM_PTableRevisionFill as begin declare @dbid int = DB_ID() SET XACT_ABORT OFF BEGIN TRY merge into QBMTableRevision as t 2using ( select distinct dt.TableName from QBM_VTablesForRevision dt where dt.IsForQBMTableRevision = 1 and dbo.QBM_FGIConfigparmValue('Common\TableRevision'3) > ' ' ) as s on t.TableName = s.TableName and t.ServerProcess = 0 when not matched by source and t.serverprocess = 0 then delete when not matched by 4target then insert ( TableName ) values ( s.Tablename ) ;  update QBMTableRevision set ServerProcess = -1 from QBMTableRevision r with (readpast) left 5outer join sys.sysprocesses p on r.ServerProcess = p.spid and p.dbid = @dbid where p.spid is null and r.ServerProcess > 0 update QBMTableRevision set RevisionDate6 = a.maxRevisionDate from QBMTableRevision u join ( select r.TableName, MAX(r.RevisionDate) as maxRevisionDate from QBMTableRevision r with (readpast) 7where r.ServerProcess <= 0 and r.RevisionDate > ' ' group by r.TableName ) as a on u.TableName = a.TableName and u.ServerProcess = 0 waitfor delay '00:00:02'8 delete QBMTableRevision where ServerProcess = -1 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow9() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return end 10