dbo.QBM_PTrafficLight

1   create   procedure QBM_PTrafficLight ( @TrafficLight int output ) as begin declare @DebugSwitch int = 0 declare @v QBM_YParameterlist declare
2 @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) join
3 sys.columns c with (readpast) on t.object_id = c.object_id where t.name = 'DialogDatabase' and c.name = 'UpdatePhase' ) begin if @DebugSwitch > 0 begin
4 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 = 1
8	'
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 if
12 exists (select top 1 1 from sys.objects o where o.name = 'JobPerformance' ) begin if @DebugSwitch > 0 begin print 'lesen auf Jobperformance' end select
13 @SQLCmd = 'select left(p.TaskName,138), left(p.ComponentClass,138)
14							from JobPerformance p
15							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 exists
23 (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 top
25 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 = 1
33		and UpdatePhase <> '
34 , str(@TrafficLight), '
35		') exec sp_executesql @SQLCmd END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow
36() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel:  select @TrafficLight = isnull(@TrafficLight, 3)  return end 
37