Back to OIM Explorer

dbo.QBM_ZProcessMarkDelete

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.921 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_FGIModuleExists 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_ZProcessMarkDelete(2  @SlotNumber int,3  @dummy1 varchar(38) = '',4  @dummy2 varchar(38) = '',5  @GenProcID varchar(38) = ''6)7AS8BEGIN9  DECLARE @Xdate DATETIME = getutcdate()10  DECLARE @XUser NVARCHAR(64) = object_name(@@procid)11  DECLARE @UnfinProcsNoRuns QBM_YSingleGuid12  DECLARE @rowCount int = 113  DECLARE @DebugSwitch int = 014  DECLARE @Stmnt nvarchar(max)15  IF NOT EXISTS(16    SELECT TOP 1 117    FROM DialogProcess18    WHERE19      ProcessState NOT IN('E', 'F'))20  BEGIN21    GOTO ende22  END23  SET XACT_ABORT OFF24  SET LOCK_TIMEOUT 50025  BEGIN TRY26    IF isnull(@XUser,27    '') = ''28    SET @xuser = 'UNKNOWN'29    SELECT30      @Stmnt = '31		select 32			P.GenprocID, 33			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 too34		from DialogProcess P35		left outer join (36						select GenprocIDOrigin37							, max(case 38								when psub.ProcessState = ''F'' then 2 39								when psub.ProcessState = ''E'' then 1 40								else 3	end41						) as ProcessStateInfo42			from QBM_VDialogProcessCollection v with (readpast)43			join Dialogprocess PSub on PSub.GenProcID = v.GenProcIDNew44			where v.GenProcIDOrigin <> v.GenProcIDNew45			group by GenprocIDOrigin46		) SubstInfo on SubStInfo.GenProcIDOrigin = p.GenprocID47		where P.ProcessState not in (''E'',''F'') --E=Ended;L=Frozen;F=Failed48			and not exists (select top 1 genprocid from DialogDBQueue with (readpast) where genprocid = P.GenProcID)49			and not exists (select top 1 genprocid from QBMDBQueueCurrent with (readpast) where genprocid = P.GenProcID and Slotnumber<>0)50			and not exists (select top 1 genprocid from QBMDBQueuePond with (readpast) where genprocid = P.GenProcID)51			and not exists (select top 1 genprocid from JobQueue with (readpast) where genprocid = P.GenProcID)52			and not exists (select top 1 genprocid from DialogDeferredOperation with (readpast) where genprocid = P.GenProcID)53		'54    IF dbo.QBM_FGIModuleExists('QER') = 155    BEGIN56      SELECT57        @Stmnt = CONCAT(@Stmnt,58        ' and not exists (select top 1 genprocid from PersonWantsOrg with (readpast) where genprocid = P.GenProcID)')59    END60    INSERT INTO @UnfinProcsNoRuns(UID_SingleGuid,61    IntProperty)62    EXEC sp_executesql @Stmnt63    IF(@DebugSwitch > 0)64    BEGIN65      SELECT66        'debug 1 cnt @UnfinProcsNoRuns',67        count(*) cnt68      FROM @UnfinProcsNoRuns69      SELECT70        'debug 1b @UnfinProcsNoRuns',71        *72      FROM @UnfinProcsNoRuns73    END74    SELECT @rowcount = 175    WHILE(@rowCount > 0)76    BEGIN77      SET @rowCount = 078      UPDATE DialogProcess79      SET ProcessState = CASE80      WHEN isnull(ErrorInfo,81      0) = 0 THEN82      'E'83      ELSE 'F'84      END85      FROM DialogProcess P86      JOIN @UnfinProcsNoRuns up87        ON P.GenprocID = up.UID_SingleGuid88      LEFT89      OUTER90      JOIN(91      SELECT92        GenprocID,93        max(sign(waserror)) AS ErrorInfo94      FROM JobHistory95      GROUP BY GenprocID) JobH96        ON JobH.GenProcID = P.GenprocID97      WHERE98        up.IntProperty = 099      SET @rowCount += @@Rowcount100      IF(@DebugSwitch > 0)101      BEGIN102        SELECT103          'after 1)',104          @rowCount AS [RowCount]105      END106      UPDATE DialogProcess107      SET ProcessState = CASE108      WHEN IntProperty = 1 THEN109      'E'110      ELSE 'F'111      END112      FROM DialogProcess P113      JOIN @UnfinProcsNoRuns up114        ON P.GenprocID = up.UID_SingleGuid115      WHERE116        up.IntProperty IN(1,117      2)118      SET @rowCount += @@Rowcount119      IF(@DebugSwitch > 0)120      BEGIN121        SELECT122          'after 2)',123          @rowCount AS [RowCount]124        SELECT125          DISTINCT 'Debug 2 Subst',126          p.GenProcID,127          p.ProcessState,128          P.Displayname129        FROM DIalogProcess P130        JOIN @UnfinProcsNoRuns up131          ON up.UID_SingleGuid = P.GenProcID132        JOIN DialogProcessSubstitute S133          ON P.GenProcID IN(S.GenProcIDNew,134        S.GenProcIDOrigin)135        SELECT136          count(*) cnt,137          ProcessState138        FROM Dialogprocess P139        JOIN @UnfinProcsNoRuns up140          ON up.UID_SingleGuid = P.GenProcID141        GROUP BY ProcessState142      END143      IF(@rowCount > 0 AND EXISTS(144        SELECT TOP 1 1145        FROM @UnfinProcsNoRuns146        WHERE147          IntProperty = 3))148      BEGIN149        UPDATE @UnfinProcsNoRuns150        SET IntProperty = -1151        WHERE152          IntProperty <> 3153        UPDATE @UnfinProcsNoRuns154        SET IntProperty = isnull(SubstInfo.ProcessStateInfo,155        0)156        FROM @UnfinProcsNoRuns OldRun157        LEFT158        OUTER159        JOIN(160        SELECT161          GenprocIDOrigin,162          max(CASE163          WHEN psub.ProcessState = 'F' THEN164          2165          WHEN psub.ProcessState = 'E' THEN166          1167        ELSE 3168        END) AS ProcessStateInfo169        FROM QBM_VDialogProcessCollection v170          WITH(READPAST)171        JOIN Dialogprocess PSub172          ON PSub.GenProcID = v.GenProcIDNew173        WHERE174          v.GenProcIDOrigin <> v.GenProcIDNew175        GROUP BY GenprocIDOrigin) SubstInfo176          ON SubStInfo.GenProcIDOrigin = OldRun.UID_SingleGuid177        WHERE178          OldRun.IntProperty = 3 AND OldRun.IntProperty <> isnull(SubstInfo.ProcessStateInfo,179        0)180        SET @rowCount = @@Rowcount181        IF(@DebugSwitch > 0)182        BEGIN183          SELECT184            'post processing, rowcount after getting post proc jobs',185            @rowCount186          SELECT187            'Group by IntProperty',188            IntProperty,189            count(*) cnt,190            min(UID_SingleGuid) AS MinP,191            max(UID_SingleGuid) AS MaxP192          FROM @UnfinProcsNoRuns193          GROUP BY IntProperty194        END195      END196      ELSE197      BEGIN198        SET @rowCount = 0199      END200    END201  END TRY202  BEGIN CATCH203    EXEC QBM_PSessionErrorAdd DEFAULT204    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()205    RAISERROR(@Rethrow,206    18,207    1)208      WITH NOWAIT209  END CATCH210  ende:211  RETURN212END

Open raw exported source

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 declare3 @rowCount int = 1 declare @DebugSwitch int = 0 declare @Stmnt nvarchar(max) if not exists (select top 1 1 from DialogProcess where ProcessState not in4 ('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 too8		from DialogProcess P9		left outer join (10						select GenprocIDOrigin11							, max(case 12								when psub.ProcessState = ''F'' then 2 13								when psub.ProcessState = ''E'' then 1 14								else 3	end15						) as ProcessStateInfo16			from QBM_VDialogProcessCollection v with (readpast)17			join Dialogprocess PSub on PSub.GenProcID = v.GenProcIDNew18			where v.GenProcIDOrigin <> v.GenProcIDNew19			group by GenprocIDOrigin20		) SubstInfo on SubStInfo.GenProcIDOrigin = p.GenprocID21		where P.ProcessState not in (''E'',''F'') --E=Ended;L=Frozen;F=Failed22			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) begin31 set @rowCount = 0  update DialogProcess set ProcessState = case when isnull(ErrorInfo, 0) = 0 then 'E' else 'F' end from DialogProcess P join @UnfinProcsNoRuns32 up on P.GenprocID = up.UID_SingleGuid left outer join ( select GenprocID, max(sign(waserror)) as ErrorInfo from JobHistory group by GenprocID ) JobH on33 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.GenprocID35 = 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.GenProcID37 join DialogProcessSubstitute S on P.GenProcID in (S.GenProcIDNew, S.GenProcIDOrigin) select count(*) cnt, ProcessState from Dialogprocess P join @UnfinProcsNoRuns38 up on up.UID_SingleGuid = P.GenProcID group by ProcessState end    if (@rowCount > 0 and exists (select top 1 1 from @UnfinProcsNoRuns where IntProperty39 = 3)) begin  update @UnfinProcsNoRuns set IntProperty = -1  where IntProperty <> 3   update @UnfinProcsNoRuns set IntProperty = isnull(SubstInfo.ProcessStateInfo40, 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 where42 v.GenProcIDOrigin <> v.GenProcIDNew group by GenprocIDOrigin ) SubstInfo on SubStInfo.GenProcIDOrigin = OldRun.UID_SingleGuid where OldRun.IntProperty43 = 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(100046) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende: return end 47