SQL Server学习：存储过程中Transaction(事务)的应用

http://blog.csdn.net/vickychen89/article/details/7470349

 

SQL Server中的Transaction，需显示开启，提交/回滚，且一个Transaction必须要有Commit

Transaction/Rollback Transaction。且Commit/Rollback一定要在return之前。

在存储过程中试用Transaction的示例：

 

 

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name='my_sp_test' AND TYPE='P') BEGIN
 DROP PROCEDURE my_sp_test;
END;
GO
create procedure my_sp_test @i int, @outstr varchar(100) out as
begin try
    begin transaction -- 事务开启
 declare @j int;
 if @i<10 begin
   set @outstr = '直接Return,并未Commit或Rollback Transaction.';
   return;
 end
 else begin
     set @outstr = '抛出自定义异常,并在异常捕获处Rollback Transaction.';
  RAISERROR (66666, -- Message id.
          16, -- Severity,
          1 -- State,
         ) ;  
 end;
 commit transaction; -- 提交事务
end try
begin catch 
 if @@ERROR=66666 begin
  -- 判断是否存在开启的事务，避免如果事务在这之前已提交或者已回滚，再次回滚会抛异常
     if(@@TRANCOUNT <> 0) begin
   rollback transaction; -- 事务回滚
     end;
 end; 
 return;
end catch;
go

 

测试存储过程，如下代码：

 

/* 第一个入参= 12, 不会产生异常
 */
DECLARE @OUTSTR_test VARCHAR(100);
exec dbo.my_sp_test 12,@OUTSTR_test out
print @OUTSTR_test ;
-- @OUTSTR_test = '抛出自定义异常,并在异常捕获处Rollback Transaction.'

/* 第一个入参= 8,执行后则会出现异常，异常信息如下行
 * 'EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。上一计数= 0，当前计数= 1。'
 */
DECLARE @OUTSTR_test_1 VARCHAR(100);
exec dbo.my_sp_test 8,@OUTSTR_test_1 out
print @OUTSTR_test_1 ;
-- @OUTSTR_test_1 = '直接Return,并未Commit或Rollback Transaction.'

/* 入参为8的测试语句执行后，之所以会出现异常，是因为Begin Transaction后，在之后
 * 的代码中未对这个Transaction进行Commit或者Rollback的操作。
 */