一个工作流程启动的支持事务的存储过程

最新推荐文章于 2024-10-11 10:43:24 发布

weixin_30568591

最新推荐文章于 2024-10-11 10:43:24 发布

阅读量71

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/samlin/archive/2009/06/08/workflow-tansaction-proc.html

本文介绍了一个SQL存储过程pStartUpFlowEvent，用于在数据库中启动工作流事件。该过程接收多个参数，包括流程名称、处理用户、截止日期等，并在事务中插入流程和流程步骤记录，更新业务表的状态。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

alter PROCEDURE pStartUpFlowEvent
(
    @Result int output,
    @FlowName nvarchar(50),
    --@DealUsers ntext,
    @DealUser nvarchar(50),
    @OutOfDate datetime,
    @IsAutoPassWhenOutDate bit,
    --@IsPass bit,
    @BusinessTableName nvarchar(50),
    @FlowStatusColumnName nvarchar(50),
    @BusinessPrimaryColumnName nvarchar(50),
    @BusinessPrimaryID nvarchar(50),
    @RequestContent ntext,
    @FlowStatus nvarchar(50),
    @CreateUserID int,
    @CreateTime datetime
    --@ModifyUserID int,
    --@ModifyTime datetime,
    --@ModifyIP nvarchar(50)


)
---procedure name:pStartUpFlowEvent
---Author:Sam Lin
---Date:2009-06-08
---Memo:启动流程
AS
DECLARE @FlowID INT
DECLARE @sql NVARCHAR(1000)
BEGIN
    IF EXISTS(SELECT 1 FROM flow WHERE BusinessTableName=@BusinessTableName AND BusinessPrimaryColumnName = @BusinessPrimaryColumnName AND BusinessPrimaryID=@BusinessPrimaryID)
    BEGIN
        PRINT '0-The Same'
        SET @Result = 0
        RETURN 0
    END

    --开始事件
    BEGIN TRAN

    --插入Flow数据
    INSERT INTO [Flow](
    [FlowName],[DealUser],[OutOfDate],[IsAutoPassWhenOutDate],[BusinessTableName],[FlowStatusColumnName],[BusinessPrimaryColumnName],[BusinessPrimaryID],[RequestContent],[FlowStatus],[CreateUserID],[CreateTime]
    )VALUES(
    @FlowName,@DealUser,@OutOfDate,@IsAutoPassWhenOutDate,@BusinessTableName,@FlowStatusColumnName,@BusinessPrimaryColumnName,@BusinessPrimaryID,@RequestContent,@FlowStatus,@CreateUserID,@CreateTime
    )
    SET @FlowID = @@IDENTITY

    IF @@ERROR <> 0
    BEGIN
        --操作失败,则事务回滚
        ROLLBACK TRAN
        --返回存储
        set @Result = -1
        PRINT '-1-Inserted Error'
        RETURN -1
    END

    --插入FlowProducure

    INSERT INTO [FlowProducure](
    [PID],[FlowID],[FlowName],[DealUser],[OutOfDate],[IsAutoPassWhenOutDate],[FlowStatus],[CreateUserID],[CreateTime]
    )VALUES(
    0,@FlowID,@FlowName,@DealUser,@OutOfDate,@IsAutoPassWhenOutDate,@FlowStatus,@CreateUserID,@CreateTime
    )

    IF @@ERROR <>0
    BEGIN
        --操作失败,则事务回滚
        ROLLBACK TRAN
        --返回存储
        SET @Result = -1
        PRINT '-1-Inserted Error'
        RETURN -1
    END

    --更新业务表
    SET @sql = 'UPDATE  '+ @BusinessTableName + ' SET ' +  @FlowStatusColumnName + '= ' + @FlowStatus + ' WHERE '+  @BusinessPrimaryColumnName + '= ''' + @BusinessPrimaryID + ''''
    EXEC sp_executesql @sql

    IF @@ROWCOUNT <> 1
    BEGIN
        --操作失败,则事务回滚
        ROLLBACK TRAN
        --返回存储
        SET @Result = -9
        PRINT '-9-Cannot find the result'
        RETURN -9
    END

    IF @@ERROR <>0
    BEGIN
        --操作失败,则事务回滚
        ROLLBACK TRAN
        --返回存储
        SET @Result = -1
        PRINT '-1-Updated Error'
        RETURN -1
    END

    --成功
    COMMIT TRAN
    SET @Result = 1
    RETURN 1
END

转载于:https://www.cnblogs.com/samlin/archive/2009/06/08/workflow-tansaction-proc.html