dbo.QBM_ZProcessMarkDelete

1   create   procedure QBM_ZProcessMarkDelete (@SlotNumber int , @dummy1 varchar(38) = '' , @dummy2 varchar(38) = '' , @GenProcID varchar(38) = ''
2 ) as begin declare @Xdate DATETIME = getutcdate() declare @XUser NVARCHAR(64) = object_name(@@procid) declare @UnfinProcsNoRuns QBM_YSingleGuid declare
3 @rowCount int = 1 declare @DebugSwitch int = 0 declare @Stmnt nvarchar(max) if not exists (select top 1 1 from DialogProcess where ProcessState not in
4 ('E','F')) begin goto ende end SET XACT_ABORT OFF set LOCK_TIMEOUT 500 BEGIN TRY if isnull(@XUser, '') = '' set @xuser = 'UNKNOWN'   select @Stmnt = '
5		select 
6			P.GenprocID, 
7			isnull(SubstInfo.ProcessStateInfo, 0) as IntProperty  -- 0=no subst; 1=subst proc is done E; 2=subst proc is done F; 3=subst proc is unfinished too
8		from DialogProcess P
9		left outer join (
10						select GenprocIDOrigin
11							, max(case 
12								when psub.ProcessState = ''F'' then 2 
13								when psub.ProcessState = ''E'' then 1 
14								else 3	end
15						) as ProcessStateInfo
16			from QBM_VDialogProcessCollection v with (readpast)
17			join Dialogprocess PSub on PSub.GenProcID = v.GenProcIDNew
18			where v.GenProcIDOrigin <> v.GenProcIDNew
19			group by GenprocIDOrigin
20		) SubstInfo on SubStInfo.GenProcIDOrigin = p.GenprocID
21		where P.ProcessState not in (''E'',''F'') --E=Ended;L=Frozen;F=Failed
22			and not exists (select top 1 genprocid from DialogDBQueue with (readpast) where genprocid = P.GenProcID)
23			and not exists (select top 1 genprocid from QBMDBQueueCurrent with (readpast) where genprocid = P.GenProcID and Slotnumber<>0)
24			and not exists (select top 1 genprocid from QBMDBQueuePond with (readpast) where genprocid = P.GenProcID)
25			and not exists (select top 1 genprocid from JobQueue with (readpast) where genprocid = P.GenProcID)
26			and not exists (select top 1 genprocid from DialogDeferredOperation with (readpast) where genprocid = P.GenProcID)
27		'
28 if dbo.QBM_FGIModuleExists('QER') = 1 begin select @Stmnt = concat(@Stmnt, ' and not exists (select top 1 genprocid from PersonWantsOrg with (readpast) where genprocid = P.GenProcID)'
29) end Insert into @UnfinProcsNoRuns(UID_SingleGuid, IntProperty) exec sp_executesql @Stmnt if (@DebugSwitch > 0) begin select 'debug 1 cnt @UnfinProcsNoRuns'
30, count(*) cnt from @UnfinProcsNoRuns select 'debug 1b @UnfinProcsNoRuns', * from @UnfinProcsNoRuns end select @rowcount = 1  while (@rowCount > 0) begin
31 set @rowCount = 0  update DialogProcess set ProcessState = case when isnull(ErrorInfo, 0) = 0 then 'E' else 'F' end from DialogProcess P join @UnfinProcsNoRuns
32 up on P.GenprocID = up.UID_SingleGuid left outer join ( select GenprocID, max(sign(waserror)) as ErrorInfo from JobHistory group by GenprocID ) JobH on
33 JobH.GenProcID = P.GenprocID where up.IntProperty = 0 set @rowCount += @@Rowcount if (@DebugSwitch > 0) begin select 'after 1)', @rowCount as [RowCount]
34 end  update DialogProcess set ProcessState = case when IntProperty = 1 then 'E' else 'F' end from DialogProcess P join @UnfinProcsNoRuns up on P.GenprocID
35 = up.UID_SingleGuid where up.IntProperty in (1, 2)  set @rowCount += @@Rowcount if (@DebugSwitch > 0) begin select 'after 2)', @rowCount as [RowCount]
36 select distinct 'Debug 2 Subst', p.GenProcID, p.ProcessState, P.Displayname from DIalogProcess P join @UnfinProcsNoRuns up on up.UID_SingleGuid = P.GenProcID
37 join DialogProcessSubstitute S on P.GenProcID in (S.GenProcIDNew, S.GenProcIDOrigin) select count(*) cnt, ProcessState from Dialogprocess P join @UnfinProcsNoRuns
38 up on up.UID_SingleGuid = P.GenProcID group by ProcessState end    if (@rowCount > 0 and exists (select top 1 1 from @UnfinProcsNoRuns where IntProperty
39 = 3)) begin  update @UnfinProcsNoRuns set IntProperty = -1  where IntProperty <> 3   update @UnfinProcsNoRuns set IntProperty = isnull(SubstInfo.ProcessStateInfo
40, 0) from @UnfinProcsNoRuns OldRun left outer join ( select GenprocIDOrigin, max(case when psub.ProcessState = 'F' then 2 when psub.ProcessState = 'E' 
41then 1 else 3 end) as ProcessStateInfo from QBM_VDialogProcessCollection v WITH (READPAST) join Dialogprocess PSub on PSub.GenProcID = v.GenProcIDNew where
42 v.GenProcIDOrigin <> v.GenProcIDNew group by GenprocIDOrigin ) SubstInfo on SubStInfo.GenProcIDOrigin = OldRun.UID_SingleGuid where OldRun.IntProperty
43 = 3 and OldRun.IntProperty <> isnull(SubstInfo.ProcessStateInfo, 0) set @rowCount = @@Rowcount if (@DebugSwitch > 0) begin select 'post processing, rowcount after getting post proc jobs'
44 , @rowCount select 'Group by IntProperty', IntProperty, count(*) cnt, min(UID_SingleGuid) as MinP, max(UID_SingleGuid) as MaxP from @UnfinProcsNoRuns 
45group by IntProperty end end  else begin  set @rowCount = 0 end end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000
46) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende: return end 
47