Отмена зависших экземпляров процессов

Решение найдет свое применение в тех случаях, когда есть необходимость отменять зависшие экземпляры процессов.

Ссылка на решение

Вся основная логика лежит на хранимых процедурах: tsp_DeleteSysProcessDataById, tsp_DeleteSysProcessDataByStartDate, tsp_UpdateSysProcessLogById, tsp_UpdateSysProcessLogByStartDate

В общих словах про процедуры

tsp_DeleteSysProcessDataById – удаляет информацию по процессу из таблиц SysActivityPrcEl, SysEntityCommonPrcEl, SysProcessIntermediateEvent, SysProcessData

tsp_DeleteSysProcessDataByStartDate – читает процессы, в логе фильтрует по дате, и запускает DeleteSysProcessDataById 

tsp_UpdateSysProcessLogById – обновляет информацию по процессу из таблиц SysProcessLog, SysProcessElementLog,

tsp_UpdateSysProcessLogByStartDate – читает процессы, в логе фильтрует по дате, и запускает UpdateSysProcessLogById

Бизнес-процесс который позволяет выбрать процесс, указать период, отменить процессы и выдать результат (к-ство отменных процессов)

Ниже отдельно SQL скрипты самих процедур

Скрипт на удаление:

SET QUOTED_IDENTIFIER ON
GO
 
IF NOT OBJECT_ID('[dbo].[tsp_DeleteSysProcessDataById]') IS NULL
BEGIN
	DROP PROCEDURE [dbo].[tsp_DeleteSysProcessDataById]
END
GO
 
CREATE PROCEDURE [dbo].[tsp_DeleteSysProcessDataById]
	@SysProcessDataId uniqueidentifier
AS
BEGIN
 
	SET NOCOUNT ON
 
	DECLARE @sysProcessDataRecordsToDelete TABLE (Id uniqueidentifier)
	DECLARE @sysProcessElementDataRecordsToDelete TABLE (Id uniqueidentifier )
 
	INSERT INTO @sysProcessDataRecordsToDelete ([Id])
		SELECT Id from SysProcessData
		WHERE ParentId = @SysProcessDataId
	OPTION (OPTIMIZE FOR (@SysProcessDataId UNKNOWN))
 
	DECLARE @COUNT bigint = (SELECT COUNT_BIG(*) FROM @sysProcessDataRecordsToDelete)
	WHILE (@COUNT) != 0
		BEGIN
			DECLARE @parentId uniqueidentifier = (SELECT TOP 1 Id from @sysProcessDataRecordsToDelete)
			DELETE FROM @sysProcessDataRecordsToDelete WHERE Id = @parentId;
			EXEC [dbo].[tsp_DeleteSysProcessDataById] @parentId
			SET @COUNT = @COUNT - 1
		END
 
	INSERT INTO @sysProcessElementDataRecordsToDelete ([Id])
		SELECT Id FROM SysProcessElementData
		WHERE SysProcessId = @SysProcessDataId
	SET @COUNT = (SELECT COUNT_BIG(*) FROM @sysProcessElementDataRecordsToDelete)
	DELETE FROM SysActivityPrcEl WHERE ProcessElementId IN (SELECT Id FROM @sysProcessElementDataRecordsToDelete)
	DELETE FROM SysEntityCommonPrcEl WHERE ProcessElementId IN (SELECT Id FROM @sysProcessElementDataRecordsToDelete)
	DELETE FROM SysProcessIntermediateEvent WHERE SysProcessElementId IN (SELECT Id FROM @sysProcessElementDataRecordsToDelete)
	DELETE FROM SysProcessData WHERE Id = @SysProcessDataId
	OPTION (OPTIMIZE FOR (@SysProcessDataId UNKNOWN))
END
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
IF NOT OBJECT_ID('[dbo].[tsp_DeleteSysProcessDataByStartDate]') IS NULL
BEGIN
	DROP PROCEDURE [dbo].[tsp_DeleteSysProcessDataByStartDate]
END
GO
 
CREATE PROCEDURE [dbo].[tsp_DeleteSysProcessDataByStartDate]
	@ProcessName nvarchar(MAX),
	@StartDate datetime,
	@EndDate datetime
AS
BEGIN
 
	SET NOCOUNT ON
 
	IF NOT OBJECT_ID('#SysProcessDataId') IS NULL
	BEGIN
		DROP TABLE #SysProcessDataId
	END
 
	CREATE TABLE #SysProcessDataId (Id uniqueidentifier)
 
	INSERT INTO #SysProcessDataId ([Id])
		SELECT DISTINCT SysProcessData.Id FROM SysProcessLog
		JOIN SysProcessData on SysProcessLog.SysSchemaId = SysProcessData.SysSchemaId
		WHERE SysProcessData.ParentId IS NULL
		AND CONVERT(date, SysProcessLog.StartDate) >= CONVERT(date, @StartDate) and CONVERT(date, SysProcessLog.StartDate) <= CONVERT(date, @EndDate) 
		AND SysProcessLog.Name = @ProcessName
		AND SysProcessLog.StatusId = 'ED2AE277-B6E2-DF11-971B-001D60E938C6'
 
	DECLARE SysProcessDataIdCursor CURSOR STATIC LOCAL FOR
	SELECT
		Id
	FROM 
		#SysProcessDataId
 
	DECLARE @COUNT int = (SELECT COUNT_BIG(*) FROM #SysProcessDataId)
 
	IF (@COUNT > 50000)
		SET @COUNT = 50000
 
	DECLARE @processDataId uniqueidentifier
 
	OPEN SysProcessDataIdCursor
	WHILE (@COUNT > 0)
		BEGIN
			FETCH NEXT FROM SysProcessDataIdCursor INTO @processDataId
			IF @@FETCH_STATUS = -1 BREAK
			IF @@FETCH_STATUS = -2 CONTINUE
			EXEC [dbo].[tsp_DeleteSysProcessDataById] @processDataId
			SET @COUNT = @COUNT - 1
		END
	CLOSE SysProcessDataIdCursor
	DEALLOCATE SysProcessDataIdCursor
 
	IF NOT OBJECT_ID('#SysProcessDataId') IS NULL
	BEGIN
		DROP TABLE #SysProcessDataId
	END
 
END
GO

Скрипт на обновление:

SET QUOTED_IDENTIFIER ON
GO
 
IF NOT OBJECT_ID('[dbo].[tsp_UpdateSysProcessLogById]') IS NULL
BEGIN
	DROP PROCEDURE [dbo].[tsp_UpdateSysProcessLogById]
END
GO
 
CREATE PROCEDURE [dbo].[tsp_UpdateSysProcessLogById]
	@SysProcessLogId uniqueidentifier
AS
BEGIN
 
	SET NOCOUNT ON
 
	DECLARE @sysProcessLogRecordsToDelete TABLE (Id uniqueidentifier)
 
	INSERT INTO @sysProcessLogRecordsToDelete ([Id])
		SELECT Id FROM SysProcessLog
		WHERE ParentId = @SysProcessLogId
	OPTION (OPTIMIZE FOR (@SysProcessLogId UNKNOWN))
 
	DECLARE @COUNT int = (SELECT COUNT(*) FROM @sysProcessLogRecordsToDelete)
	WHILE (@COUNT) != 0
		BEGIN
			DECLARE @parentId uniqueidentifier = (SELECT TOP 1 Id from @sysProcessLogRecordsToDelete)
 
			Update SysProcessLog 
			Set StatusId=( select id from SysProcessStatus where id = '1BE78F3E-234D-4D6A-869A-DC07253FD2F3' ) 
			Where id =@parentId
 
			EXEC [dbo].[tsp_UpdateSysProcessLogById] @parentId
			SET @COUNT = @COUNT - 1
		END
 
	Update SysProcessLog 
	Set CompleteDate = GETUTCDATE(), StatusId=( select id from SysProcessStatus where id = '1BE78F3E-234D-4D6A-869A-DC07253FD2F3' ) 
	Where id =@SysProcessLogId AND StatusId = 'ED2AE277-B6E2-DF11-971B-001D60E938C6'
 
 
	UPDATE SysProcessElementLog Set StatusId = (select Id from SysProcessStatus where Id = '1BE78F3E-234D-4D6A-869A-DC07253FD2F3'), CompleteDate = GETUTCDATE() where SysProcessId = @SysProcessLogId 
	and StatusId= 'ED2AE277-B6E2-DF11-971B-001D60E938C6' 
 
 
END
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
IF NOT OBJECT_ID('[dbo].[tsp_UpdateSysProcessLogByStartDate]') IS NULL
BEGIN
	DROP PROCEDURE [dbo].[tsp_UpdateSysProcessLogByStartDate]
END
GO
 
CREATE PROCEDURE [dbo].[tsp_UpdateSysProcessLogByStartDate]
	@ProcessName nvarchar(MAX),
	@StartDate datetime,
	@EndDate datetime
AS
BEGIN
 
	SET NOCOUNT ON
 
	IF NOT OBJECT_ID('#SysProcessLogId') IS NULL
	BEGIN
		DROP TABLE #SysProcessLogId
	END
 
	CREATE TABLE #SysProcessLogId (Id uniqueidentifier)
 
	INSERT INTO #SysProcessLogId ([Id])
		SELECT Top 100000 SysProcessLog.Id FROM SysProcessLog
		WHERE SysProcessLog.ParentId IS NULL
		AND CONVERT(date, SysProcessLog.StartDate) >= CONVERT(date, @StartDate) 
		AND CONVERT(date, SysProcessLog.StartDate) <= CONVERT(date, @EndDate) 
		AND SysProcessLog.Name = @ProcessName 
 
	CREATE TABLE #SysProcessExec (Id uniqueidentifier)
 
	INSERT INTO #SysProcessExec ([Id])
		SELECT Top 100000 SysProcessLog.Id FROM SysProcessLog
		WHERE SysProcessLog.ParentId IS NULL
		AND CONVERT(date, SysProcessLog.StartDate) >= CONVERT(date, @StartDate) 
		AND CONVERT(date, SysProcessLog.StartDate) <= CONVERT(date, @EndDate) 
		AND SysProcessLog.Name = @ProcessName 
		AND SysProcessLog.StatusId = 'ED2AE277-B6E2-DF11-971B-001D60E938C6'
 
	DECLARE SysProcessLogIdCursor CURSOR STATIC LOCAL FOR
	SELECT
		Id
	FROM 
		#SysProcessLogId
 
	DECLARE @COUNT int = (SELECT COUNT_BIG(*) FROM #SysProcessLogId)
 
	DECLARE @processLogId uniqueidentifier
 
	OPEN SysProcessLogIdCursor
	WHILE (@COUNT > 0)
		BEGIN
			FETCH NEXT FROM SysProcessLogIdCursor INTO @processLogId
			IF @@FETCH_STATUS = -1 BREAK
			IF @@FETCH_STATUS = -2 CONTINUE
			EXEC [dbo].[tsp_UpdateSysProcessLogById] @processLogId
			SET @COUNT = @COUNT - 1
		END
	CLOSE SysProcessLogIdCursor
	DEALLOCATE SysProcessLogIdCursor
	INSERT INTO MassCancellationProcessesLog (StartDate, EndDate, DisableProcessCount, Process)	VALUES(CONVERT(date, @StartDate), CONVERT(date, @EndDate), (SELECT COUNT(*) FROM #SysProcessExec), @ProcessName)
	IF NOT OBJECT_ID('#SysProcessLogId') IS NULL
	BEGIN
		DROP TABLE #SysProcessLogId
	END
	IF NOT OBJECT_ID('#SysProcessExec') IS NULL
	BEGIN
		DROP TABLE #SysProcessExec
	END
 
END
GO