转：http://www.cnblogs.com/dannyli/archive/2012/11/29/2794845.html
/************************************************************************************************
** 功能：根据实例ID，删除[K2ServerLog]中的流程实例数据（物理删除，不可恢复，操作前请备份）
** 作者：Danny,Li [xing.dong.li@163.com]
** 日期：2012-11-28
** 版本：v121128
************************************************************************************************
** 返回值 返回值描述
** ------ --------------------------------------------------------------
**
**
************************************************************************************************
** 修订日期 修订作者 修订描述
** ---------- -------- -----------------------------------------------
**
**
************************************************************************************************/
USE [K2ServerLog]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uSP_DeleteK2ServerLogProcInst]
@procInstID INT -- Process Instance ID
AS
SET NOCOUNT ON
BEGIN
PRINT 'Delete process instance from K2ServerLog 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 _ActInst'
DELETE _ActInst WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInst'
SELECT @iError = @@ERROR
END
IF @iError = 0
BEGIN
PRINT 'Removing process instance from _ActInstAudit'
DELETE _ActInstAudit WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstAudit'
SELECT @iError = @@ERROR
END
IF @iError = 0
BEGIN
PRINT 'Removing process instance from _ActInstDest'
DELETE _ActInstDest WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstDest'
SELECT @iError = @@ERROR
END
IF @iError = 0
BEGIN
PRINT 'Removing process instance from _ActInstDestData'
DELETE _ActInstDestData WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstDestData'
SELECT @iError = @@ERROR
END
IF @iError = 0
BEGIN
PRINT 'Removing process instance from _ActInstDestDataAudit'
DELETE _ActInstDestDataAudit WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstDestDataAudit'
SELECT @iError = @@ERROR
END
IF @iError = 0
BEGIN
PRINT 'Removing process instance from _ActInstDestXml'
DELETE _ActInstDestXml WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstDestXml'
SELECT @iError = @@ERROR
END
IF @iError = 0
BEGIN
PRINT 'Removing process instance from _ActInstDestXmlAudit'
DELETE _ActInstDestXmlAudit WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstDestXmlAudit'
SELECT @iError = @@ERROR
END
IF @iError = 0
BEGIN
PRINT 'Removing process instance from _EscInst'
DELETE _EscInst WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _EscInst'
SELECT @iError = @@ERROR
END
IF @iError = 0
BEGIN
PRINT 'Removing process instance from _EventInst'
DELETE _EventInst WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _EventInst'
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 _LogBatch'
DELETE _LogBatch WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _LogBatch'
SELECT @iError = @@ERROR
END
IF @iError = 0
BEGIN
PRINT 'Removing process instance from _LineInst'
DELETE _LineInst WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _LineInst'
SELECT @iError = @@ERROR
END
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 _ProcInstAudit'
DELETE _ProcInstAudit WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcInstAudit'
SELECT @iError = @@ERROR
END
IF @iError = 0
BEGIN
PRINT 'Removing process instance from _ProcInstData'
DELETE _ProcInstData WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcInstData'
SELECT @iError = @@ERROR
END
IF @iError = 0
BEGIN
PRINT 'Removing process instance from _ProcInstDataAudit'
DELETE _ProcInstDataAudit WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcInstDataAudit'
SELECT @iError = @@ERROR
END
IF @iError = 0
BEGIN
PRINT 'Removing process instance from _ProcInstXml'
DELETE _ProcInstXml WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInst'
SELECT @iError = @@ERROR
END
IF @iError = 0
BEGIN
PRINT 'Removing process instance from _ProcInstXmlAudit'
DELETE _ProcInstXmlAudit WHERE ProcInstID = @procInstID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcInstXmlAudit'
SELECT @iError = @@ERROR
END
IF @iError <> 0
BEGIN
ROLLBACK TRANSACTION
PRINT 'Delete process instance from K2ServerLog for ID = '+ CAST(@procInstID AS VARCHAR(50)) +' rollback'
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT 'Delete process instance from K2ServerLog for ID = '+ CAST(@procInstID AS VARCHAR(50)) +' commit'
END
END