一个工作流程启动的支持事务的存储过程
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
(
@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