Back to OIM Explorer

dbo.QBM_PTrafficLight

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.620 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_FCVStringToInt source text reference
references source dbo.QBM_FGISessionErrorRethrow source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE PROCEDURE QBM_PTrafficLight(2  @TrafficLight int OUTPUT3)4AS5BEGIN6  DECLARE @DebugSwitch int = 07  DECLARE @v QBM_YParameterlist8  DECLARE @SQLCmd nvarchar(max)9  DECLARE @Comp QBM_YParameterlist10  SET XACT_ABORT OFF11  BEGIN TRY12    IF NOT EXISTS(13      SELECT TOP 1 114      FROM sys.tables t15      WITH(readpast)16    JOIN sys.columns c17      WITH(readpast)18      ON t.object_id = c.object_id19    WHERE20      t.name = 'DialogDatabase' AND c.name = 'UpdatePhase')21    BEGIN22      IF @DebugSwitch > 023      BEGIN24        print 'noch alter Stand'25      END26      GOTO endLabel27    END28    SELECT29      @SQLCmd = '30select top 1 str(d.UpdatePhase)31	from DialogDatabase d with (readpast)32	where d.IsMainDatabase = 133	'34    INSERT INTO @v(Parameter1)35    EXEC sp_executesql @SQLCmd36    SELECT37      TOP 1 @TrafficLight = dbo.QBM_FCVStringToInt(v.Parameter1,38      0)39    FROM @v v40    IF @TrafficLight IN(0,41    4)42    BEGIN43      IF @DebugSwitch > 044      BEGIN45        print 'es ist grün, jedenfalls für mich'46      END47      GOTO endLabel48    END49    IF @TrafficLight = 150    BEGIN51      IF @DebugSwitch > 052      BEGIN53        print 'wir haben gelb'54      END55      INSERT INTO @Comp(Parameter1,56      Parameter2)57      SELECT58        r.TaskName,59        r.ComponentClass60      FROM QBM_VTrafficLightLongRunner r61      IF EXISTS(62        SELECT TOP 1 163        FROM sys.objects o64        WHERE65          o.name = 'JobPerformance')66      BEGIN67        IF @DebugSwitch > 068        BEGIN69          print 'lesen auf Jobperformance'70        END71        SELECT72          @SQLCmd = 'select left(p.TaskName,138), left(p.ComponentClass,138)73							from JobPerformance p74							where p.ComponentClass <> ''VI.JobService.JobComponents.AutoUpdateComponent''  -- die haben wir mit %75							group by left(p.TaskName,138), left(p.ComponentClass,138)76							having avg(p.CountPerMinute) < 11'77        INSERT INTO @Comp(Parameter1,78        Parameter2)79        EXEC sp_executesql @SQLCmd80      END81      ELSE82      BEGIN83        IF @DebugSwitch > 084        BEGIN85          print 'keine Jobperformance'86        END87      END88      IF @DebugSwitch > 089      BEGIN90        SELECT91          c.Parameter1 AS TaskName,92          c.Parameter2 AS ComponentClass93        FROM @Comp c94      END95      IF EXISTS(96        SELECT TOP 1 197        FROM Jobqueue o98        WITH(readpast)99      JOIN @Comp v100        ON o.TaskName LIKE v.Parameter1 AND o.ComponentClass = v.Parameter2101      WHERE102        o.Ready2EXE IN(N 'LOADED', N 'PROCESSING'))103      BEGIN104        IF @DebugSwitch > 0105        BEGIN106          print 'noch potentielle Langläufer unterwegs'107        END108        GOTO endLabel109      END110      ELSE111      BEGIN112        IF @DebugSwitch > 0113        BEGIN114          print 'wir gehen auf orange'115        END116        SELECT @TrafficLight = 2117        GOTO endeMitUpdate118      END119    END120    IF @TrafficLight = 2121    BEGIN122      IF @DebugSwitch > 0123      BEGIN124        print 'wir haben orange'125      END126      IF EXISTS(127        SELECT TOP 1 1128        FROM JobQueue q129        WITH(readpast)130      WHERE131        q.Ready2EXE IN(N 'LOADED', N 'PROCESSING'))132      BEGIN133        IF @DebugSwitch > 0134        BEGIN135          print 'jobqueue hat noch was'136        END137        GOTO endLabel138      END139      IF EXISTS(140        SELECT TOP 1 1141        FROM DialogDBQueue q142        WITH(readpast)143      WHERE144        q.Generation <> 0 AND q.Generation < 20145      UNION all146      SELECT TOP 1 1147      FROM QBMDBQueueCurrent q148        WITH(readpast)149      WHERE150        q.SlotNumber != 0 AND q.Generation < 20)151      BEGIN152        IF @DebugSwitch > 0153        BEGIN154          print 'dbqueue hat noch was'155        END156        GOTO endLabel157      END158      IF @DebugSwitch > 0159      BEGIN160        print 'warteschlangen leer'161      END162      SELECT @TrafficLight = 3163      GOTO endeMitUpdate164    END165    IF @TrafficLight = 3166    BEGIN167      IF @DebugSwitch > 0168      BEGIN169        print 'wir haben rot'170      END171      GOTO endLabel172    END173    endeMitUpdate:174    SELECT175      @SQLCmd = CONCAT('176	update DialogDatabase 177		set UpdatePhase = ',178      str(@TrafficLight),179      '180		, XDateUpdated = getutcdate(), XUserUpdated = ''',181      object_name(@@procid),182      ''' 183		where IsMainDatabase = 1184		and UpdatePhase <> ',185      str(@TrafficLight),186      '187		')188    EXEC sp_executesql @SQLCmd189  END TRY190  BEGIN CATCH191    EXEC QBM_PSessionErrorAdd DEFAULT192    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()193    RAISERROR(@Rethrow,194    18,195    1)196      WITH NOWAIT197  END CATCH198  endLabel:199  SELECT200    @TrafficLight = isnull(@TrafficLight,201    3)202  RETURN203END

Open raw exported source

1   create   procedure QBM_PTrafficLight ( @TrafficLight int output ) as begin declare @DebugSwitch int = 0 declare @v QBM_YParameterlist declare2 @SQLCmd nvarchar(max) declare @Comp QBM_YParameterlist SET XACT_ABORT OFF BEGIN TRY if not exists (select top 1 1 from sys.tables t with (readpast) join3 sys.columns c with (readpast) on t.object_id = c.object_id where t.name = 'DialogDatabase' and c.name = 'UpdatePhase' ) begin if @DebugSwitch > 0 begin4 print 'noch alter Stand' end goto endLabel end select @SQLCmd = '5select top 1 str(d.UpdatePhase)6	from DialogDatabase d with (readpast)7	where d.IsMainDatabase = 18	'9 insert into @v(Parameter1) exec sp_executesql @SQLCmd select top 1 @TrafficLight = dbo.QBM_FCVStringToInt(v.Parameter1, 0) from @v v if @TrafficLight 10in( 0, 4)  begin if @DebugSwitch > 0 begin print 'es ist grün, jedenfalls für mich' end goto endLabel end if @TrafficLight = 1  begin if @DebugSwitch >11 0 begin print 'wir haben gelb' end  insert into @Comp(Parameter1, Parameter2) select r.TaskName, r.ComponentClass from QBM_VTrafficLightLongRunner r if12 exists (select top 1 1 from sys.objects o where o.name = 'JobPerformance' ) begin if @DebugSwitch > 0 begin print 'lesen auf Jobperformance' end select13 @SQLCmd = 'select left(p.TaskName,138), left(p.ComponentClass,138)14							from JobPerformance p15							where p.ComponentClass <> ''VI.JobService.JobComponents.AutoUpdateComponent''  -- die haben wir mit %16							group by left(p.TaskName,138), left(p.ComponentClass,138)17							having avg(p.CountPerMinute) < 11'18 insert into @Comp(Parameter1, Parameter2) exec sp_executesql @SQLCmd end else begin if @DebugSwitch > 0 begin print 'keine Jobperformance' end end if 19@DebugSwitch > 0 begin select c.Parameter1 as TaskName, c.Parameter2 as ComponentClass from @Comp c end if exists (select top 1 1 from Jobqueue o with 20(readpast) join  @Comp v on o.TaskName like v.Parameter1   and o.ComponentClass = v.Parameter2        where o.Ready2EXE in ( N'LOADED', N'PROCESSING') 21) begin  if @DebugSwitch > 0 begin print 'noch potentielle Langläufer unterwegs' end goto endLabel end else begin if @DebugSwitch > 0 begin print 'wir gehen auf orange'22 end select @TrafficLight = 2     goto endeMitUpdate  end end  if @TrafficLight = 2  begin if @DebugSwitch > 0 begin print 'wir haben orange' end if exists23 (select top 1 1 from JobQueue q with (readpast)    where q.Ready2EXE in ( N'LOADED', N'PROCESSING') )        begin if @DebugSwitch > 0 begin print 'jobqueue hat noch was'24 end goto endLabel end if exists (select top 1 1 from DialogDBQueue q with (readpast) where q.Generation <> 0  and q.Generation < 20 union all select top25 1 1 from QBMDBQueueCurrent q with (readpast) where q.SlotNumber != 0 and q.Generation < 20 ) begin if @DebugSwitch > 0 begin print 'dbqueue hat noch was'26 end goto endLabel end if @DebugSwitch > 0 begin print 'warteschlangen leer' end select @TrafficLight = 3     goto endeMitUpdate end  if @TrafficLight 27= 3  begin if @DebugSwitch > 0 begin print 'wir haben rot' end  goto endLabel end endeMitUpdate: select @SQLCmd = CONCAT('28	update DialogDatabase 29		set UpdatePhase = '30 , str(@TrafficLight), '31		, XDateUpdated = getutcdate(), XUserUpdated = ''', object_name(@@procid) , ''' 32		where IsMainDatabase = 133		and UpdatePhase <> '34 , str(@TrafficLight), '35		') exec sp_executesql @SQLCmd END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow36() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel:  select @TrafficLight = isnull(@TrafficLight, 3)  return end 37