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

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