SqlServer2008中事务使用的一些细节

测试存储过程代码时候碰到了一个事务的细节问题，是在使用if else语句时候，报错begin tran和commit tran不配对，以前使用时候并没怎么留意这些小细节，所以出现了一些想当然的错误

存储过程如下：

Alter PROCEDURE [dbo].[proc_test] 
	@id int,
	@result	INT=-1 OUTPUT,
	@msg AS varchar(50) OUTPUT
AS
BEGIN
	BEGIN TRAN
	BEGIN TRY
		DECLARE @num INT=0
		SELECT  @num=num from temp_num where id=@id
		IF (@num=0)
		BEGIN
			insert into temp_num values(floor(rand()*200)-100);--插入-100到100的随机数
			SET @msg='处理成功'
			SET @result=1
		END
		ELSE
		BEGIN
			SET @msg='处理失败'
			SET @result=0
			RAISERROR(@msg,16,1)
		END
	COMMIT TRAN
	END TRY
	BEGIN CATCH
		SET @msg=ERROR_MESSAGE()
		SET @result=0
		ROLLBACK TRAN
	END CATCH
	
	RETURN	@result
END

现在因为业务需求，增加了一个else if分支，调整后如下：

Alter PROCEDURE [dbo].[proc_test] 
	@id int,
	@result	INT=-1 OUTPUT,
	@msg AS varchar(50) OUTPUT
AS
BEGIN
	BEGIN TRAN
	BEGIN TRY
		DECLARE @num INT=0
		SELECT  @num=num from temp_num where id=@id
		IF (@num=0)
		BEGIN
			insert into temp_num values(floor(rand()*200)-100);--插入-100到100的随机数
			SET @msg='处理成功'
			SET @result=1
		END
		ELSE IF(@num<0)
		BEGIN
			update temp_num set num=-1*num where id=@id;--将负数变正
			ROLLBACK TRAN
		END
		ELSE
		BEGIN
			SET @msg='处理失败'
			SET @result=0
			RAISERROR(@msg,16,1)
		END
	COMMIT TRAN
	END TRY
	BEGIN CATCH
		SET @msg=ERROR_MESSAGE()
		SET @result=0
		ROLLBACK TRAN
	END CATCH
	
	RETURN	@result
END

那么问题来了，该存储过程执行到新增的else if分支就直接报错了“ROLLBACK TRANSACTION 请求没有对应的 BEGIN TRANSACTION。”

ELSE IF(@num<0)
BEGIN
update temp_num set num=-1*num where id=@id;--将负数变正
ROLLBACK TRAN
END

写c#代码时候

public int TestMethod()

{

if(a>0)

{

a=a+1;

}

else if(a<0)

{

return 0;

}

else

{

a=a-1;

}

return 1;

}

c#中这么写，没毛病，编译器也不会报错，但是想当然的把begin tran、commit tran/rollback tran当成return这样的用就有问题了，sqlserver语法检查通不过！

begin tran 和 commit tran、rollback tran的配对，在碰到if else时候，如果你在分支里写了commit tran、rollback tran，那么每个分支都需要提交或者回滚，不能部分写分支里，部分写if else语句结束之后

所以调整后的存储过程

Alter PROCEDURE [dbo].[proc_test] 
	@id int,
	@result	INT=-1 OUTPUT,
	@msg AS varchar(50) OUTPUT
AS
BEGIN
	BEGIN TRAN
	BEGIN TRY
		DECLARE @num INT=0
		SELECT  @num=num from temp_num where id=@id
		IF (@num=0)
		BEGIN
			insert into temp_num values(floor(rand()*200)-100);--插入-100到100的随机数
			SET @msg='处理成功'
			SET @result=1
			COMMIT TRAN
		END
		ELSE IF(@num<0)
		BEGIN
			update temp_num set num=-1*num where id=@id;--将负数变正
			COMMIT TRAN
		END
		ELSE
		BEGIN
			ROLLBACK TRAN
			SET @msg='处理失败'
			SET @result=0
			RAISERROR(@msg,16,1)
		END
	
	END TRY
	BEGIN CATCH
		SET @msg=ERROR_MESSAGE()
		SET @result=0
		ROLLBACK TRAN
	END CATCH
	
	RETURN	@result
END

也就是说，如果你在分支里使用了commit、rollback，那么每个分支里都必须提交或者回滚，并且if else语句之后不能再次提交或者回滚。这样写起来比较麻烦，其实最好的做法是提前定义一个变量，if else中对变量赋值，结尾判断值再进行提交或者回滚操作。

此外，begin tran写在begin if之内或者之外都不影响catch语句中的回滚