K2 Blackpearl中从数据库直接删除流程实例之K2Server表

转：http://www.cnblogs.com/dannyli/archive/2012/11/29/2794772.html

/************************************************************************************************
**  功能：根据实例ID，删除[K2Server]中的流程实例数据（物理删除，不可恢复，操作前请备份）
**  作者：Danny,Li [xing.dong.li@163.com]
**  日期：2012-11-28
**  版本：v121128
************************************************************************************************
**  返回值        返回值描述
**  ------        --------------------------------------------------------------
**   
**
************************************************************************************************
**  修订日期        修订作者        修订描述
**  ----------    --------    -----------------------------------------------
**   
**   
************************************************************************************************/

USE [K2Server]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[uSP_DeleteK2ServerProcInst]
    @procInstID INT        -- Process Instance ID
AS

SET NOCOUNT ON

BEGIN
    PRINT 'Delete process instance from K2Server for ID = '+ CAST(@procInstID AS VARCHAR(50)) +' starting'
   
    DECLARE @iError INT
    SELECT @iError = @@Error
   
    BEGIN TRANSACTION
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _ProcInst'
        DELETE _ProcInst WHERE ID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcInst'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _Async'
        DELETE _Async WHERE ProcInstID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _Async'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _Code'
        DELETE _Code FROM (_Code INNER JOIN _ErrorLog E ON _Code.ID = E.CodeID ) WHERE E.ProcInstID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _Code'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _ErrorLog'
        DELETE _ErrorLog WHERE ProcInstID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ErrorLog'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _ProcInstDestQueue'
        DELETE _ProcInstDestQueue WHERE ProcInstID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcInstDestQueue'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _FieldOnDemand'
        DELETE _FieldOnDemand WHERE ProcInstID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _FieldOnDemand'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _IPCAsync'
        DELETE _IPCAsync FROM (_IPCAsync ia    JOIN _IPC i ON ia.ItemID = i.ID)　　　　　　　　WHERE SrcProcInstID = @procInstID AND ia.[Type] IN (1, 3, 4)
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _IPCAsync'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _IPCAsync'
        DELETE _IPCAsync FROM (_IPCAsync ia JOIN _IPCReturn i ON ia.ItemID = i.DstProcInstID)　　　　　　　　WHERE DstProcInstID = @procInstID AND ia.[Type] IN (2)
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _IPCAsync'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _IPC'
        DELETE _IPC WHERE SrcProcInstID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _IPC'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _IPC'
        DELETE _IPC WHERE DstProcInstID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _IPC'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _IPCReturn'
        DELETE _IPCReturn WHERE SrcProcInstID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _IPCReturn'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _IPCReturn'
        DELETE _IPCReturn WHERE DstProcInstID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _IPCReturn'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _Log'
        DELETE _Log WHERE ProcInstID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _Log'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _ServerList'
        DELETE _ServerList WHERE ProcInstID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ServerList'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _WorklistHeader'
        DELETE _WorklistHeader WHERE ProcInstID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _WorklistHeader'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _WorklistSlot'
        DELETE _WorklistSlot WHERE ProcInstID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _WorklistSlot'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _ActionActInstRights'
        DELETE _ActionActInstRights WHERE ProcInstID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActionActInstRights'
        SELECT @iError = @@ERROR
    END
   
    IF @iError = 0
    BEGIN
        PRINT 'Removing process instance from _ActionActInstShared'
        DELETE _ActionActInstShared WHERE ProcInstID = @procInstID
        PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActionActInstShared'
        SELECT @iError = @@ERROR
    END
   
    IF @iError <> 0
    BEGIN
        ROLLBACK TRANSACTION
        PRINT 'Delete process instance from K2Server for ID = '+ CAST(@procInstID AS VARCHAR(50)) +' rollback'
    END
    ELSE
    BEGIN
        COMMIT TRANSACTION
        PRINT 'Delete process instance from K2Server for ID = '+ CAST(@procInstID AS VARCHAR(50)) +' commit'
    END
END