一個工作流程啟動的支援事務的存儲過程

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

        --操作失敗,則事務復原

        ROLLBACK TRAN

        --傳回存儲

        set @Result = -1

        PRINT '-1-Inserted Error'

        RETURN -1

    --插入FlowProducure

    INSERT INTO [FlowProducure](

    [PID],[FlowID],[FlowName],[DealUser],[OutOfDate],[IsAutoPassWhenOutDate],[FlowStatus],[CreateUserID],[CreateTime]

    0,@FlowID,@FlowName,@DealUser,@OutOfDate,@IsAutoPassWhenOutDate,@FlowStatus,@CreateUserID,@CreateTime

    IF @@ERROR <>0

        SET @Result = -1

    --更新業務表

    SET @sql = 'UPDATE  '+ @BusinessTableName + ' SET ' +  @FlowStatusColumnName + '= ' + @FlowStatus + ' WHERE '+  @BusinessPrimaryColumnName + '= ''' + @BusinessPrimaryID + ''''

    EXEC sp_executesql @sql

    IF @@ROWCOUNT <> 1

        SET @Result = -9

        PRINT '-9-Cannot find the result'

        RETURN -9

        PRINT '-1-Updated Error'

    --成功

    COMMIT TRAN

    SET @Result = 1

    RETURN 1

END

<b>作者贊賞</b>

<a href="http://union.dangdang.com/transfer.php?from=P-262177&amp;ad_type=10&amp;sys_id=1&amp;backurl=http%3A%2F%2Fbook.dangdang.com%2F">當當計算書籍 5-8折</a>

本文轉自Sam Lin部落格部落格園部落格，原文連結：http://www.cnblogs.com/samlin/archive/2009/06/08/workflow-tansaction-proc.html，如需轉載請自行聯系原作者