Back to OIM Explorer

dbo.QBM_PTableRevisionSetMulti_i

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.295 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_PTableRevisionSetMulti source text reference

References

Referenced By

Complete Source

SQL116 lines
1CREATE PROCEDURE QBM_PTableRevisionSetMulti_i(2  @TableNamesString varchar(max)3)4AS5BEGIN6  DECLARE @ServerProcess int = @@spid7  DECLARE @JustNow datetime = getutcdate()8  DECLARE @DebugSwitch int = 09  DECLARE @TableNamesForRevision QBM_YParameterList10  DECLARE @CurrentDeadlock_priority int11  SET XACT_ABORT OFF12  BEGIN TRY13    INSERT INTO @TableNamesForRevision(Parameter1)14    SELECT s.value15    FROM string_split(@TableNamesString,16    ',') AS s17    SELECT TOP 1 @CurrentDeadlock_priority = s.deadlock_priority18    FROM sys.dm_exec_sessions s19    WHERE20      s.session_id = @@spid21    SET deadlock_priority low22    SET lock_timeout 5023    DECLARE @TablesAndTimes QBM_YCursorBuffer24    INSERT INTO @TablesAndTimes(Ident1,25    DateTime1,26    Bit1)27    SELECT28      r.Parameter1,29      dt.RevisionDate,30    CASE31      WHEN dt.RevisionDate IS NULL THEN32    033      WHEN dt.RevisionDate < DATEADD(SS,34    -10,35    @JustNow) THEN36    137    ELSE 038    END39    FROM @TableNamesForRevision r40    LEFT41    OUTER42    JOIN QBMTableRevision dt43      WITH(nolock)44      ON dt.TableName = r.Parameter1 AND dt.ServerProcess = @ServerProcess45    IF @DebugSwitch > 046    BEGIN47      SELECT48        Ident1,49        DateTime1,50        Bit151      FROM @TablesAndTimes52    END53    IF EXISTS(54      SELECT TOP 1 155      FROM @TablesAndTimes t56      WHERE57        t.DateTime1 IS NULL)58    BEGIN59      IF @DebugSwitch > 060      BEGIN61        print 'insert machen'62      END63      INSERT INTO QBMTableRevision(TableName,64      RevisionDate,65      ServerProcess)66      SELECT67        t.Ident1,68        @JustNow,69        @ServerProcess70      FROM @TablesAndTimes t71      WHERE72        t.DateTime1 IS NULL AND NOT EXISTS(73      SELECT TOP 1 174      FROM QBMTableRevision e75        WITH(readpast)76      WHERE77        e.TableName = t.Ident1 AND e.ServerProcess = @ServerProcess)78    END79    IF EXISTS(80      SELECT TOP 1 181      FROM @TablesAndTimes t82      WHERE83        t.Bit1 = 1)84    BEGIN85      IF @DebugSwitch > 086      BEGIN87        print 'update  machen'88      END89      UPDATE QBMTableRevision90      SET RevisionDate = @JustNow91      FROM QBMTableRevision r92      JOIN @TablesAndTimes t93        ON r.TableName = t.Ident1 AND r.ServerProcess = @ServerProcess94      WHERE95        t.Bit1 = 1 AND NOT EXISTS(96      SELECT TOP 1 197      FROM QBMTableRevision e98        WITH(nolock)99      WHERE100        e.TableName = t.Ident1 AND e.RevisionDate > DATEADD(SS, -10, @JustNow))101      IF @DebugSwitch > 0102      BEGIN103        SELECT *104        FROM QBMTableRevision r105        JOIN @TablesAndTimes t106          ON r.TableName = t.Ident1 AND r.ServerProcess = @ServerProcess107      END108    END109  END TRY110  BEGIN CATCH111  END CATCH112  endLabel:113  SET deadlock_priority @CurrentDeadlock_priority114  SET lock_timeout -1115  RETURN116END
Open raw exported source
SQL ยท Raw16 lines
1     create   procedure QBM_PTableRevisionSetMulti_i (  @TableNamesString varchar(max)  ) as begin declare @ServerProcess int = @@spid declare @JustNow2 datetime = getutcdate()  declare @DebugSwitch int = 0 declare @TableNamesForRevision QBM_YParameterList declare @CurrentDeadlock_priority int SET XACT_ABORT3 OFF BEGIN TRY                 insert into @TableNamesForRevision(Parameter1) select s.value from string_split(@TableNamesString, ',') as s select top 41 @CurrentDeadlock_priority = s.deadlock_priority FROM sys.dm_exec_sessions s where s.session_id = @@spid set deadlock_priority low  set lock_timeout 505  declare @TablesAndTimes QBM_YCursorBuffer    insert into @TablesAndTimes (Ident1,DateTime1, Bit1) select r.Parameter1  , dt.RevisionDate, case when dt.RevisionDate6 is null then 0 when dt.RevisionDate < DATEADD(SS, -10 , @JustNow) then 1 else 0 end from @TableNamesForRevision r left outer join QBMTableRevision dt 7with (nolock) on dt.TableName = r.Parameter1 and dt.ServerProcess = @ServerProcess if @DebugSwitch > 0 begin select Ident1,DateTime1, Bit1 from @TablesAndTimes8 end if exists (select top 1 1 from @TablesAndTimes t where t.DateTime1  is null ) begin  if @DebugSwitch > 0 begin print 'insert machen' end insert into9 QBMTableRevision(TableName, RevisionDate, ServerProcess ) select t.Ident1, @JustNow, @ServerProcess from @TablesAndTimes t where t.DateTime1 is null and10 not exists (select top 1 1 from QBMTableRevision e with (readpast) where e.TableName = t.Ident1 and e.ServerProcess = @ServerProcess ) end if exists (select11 top 1 1 from @TablesAndTimes t where t.Bit1 = 1 ) begin  if @DebugSwitch > 0 begin print 'update  machen' end update QBMTableRevision set RevisionDate12 = @JustNow from QBMTableRevision r join @TablesAndTimes t on r.TableName = t.Ident1 and r.ServerProcess = @ServerProcess where t.Bit1 = 1  and not exists13 (select top 1 1 from QBMTableRevision e with (nolock) where e.TableName = t.Ident1 and e.RevisionDate > DATEADD(SS, -10 , @JustNow) ) if @DebugSwitch 14> 0 begin select * from QBMTableRevision r join @TablesAndTimes t on r.TableName = t.Ident1 and r.ServerProcess = @ServerProcess end end  END TRY BEGIN15 CATCH   END CATCH endLabel: set deadlock_priority @CurrentDeadlock_priority set lock_timeout -1 return  end 16