一个简单的存储过程使用事务的例子

一、存储过程中使用事务的简单语法

       在存储过程中使用事务时非常重要，使用数据库可以保持数据的关联完整性，在SQL Server存储过程中使用事务也很简单，举个例子：

Create  Procedure MyProcedure
(
   @Param1 nvarchar(10),
   @Param2 nvarchar(10)
)
AS
    Begin  
              Set NOCUNT ON;
              Set XACT_ABORT on;
              Begin  Tran
                      Delete  from  table1 where name='abc';
                      insert   into   table2  values(value1,value2,value3);  
              Commit  Tran
    End
   

说明：1、使用存储过程执行事务，需要开启XACT_ABORT参数（默认值Off）,将该参数设置On,表示当执行事务时，如果出错，会将transcation设置为uncommittable状态，那么在语句块批处理结束后；如果该参数设置为Off,表示当执行事务时，如果出错，出错的语句将不会执行，其他正确的操作继续执行。

2、当Set Nocunt为ON时，不返回计数（计数表示受Transact-SQL语句影响的行数，例如在SQL Server查询分析器中执行一个Delete操作后，下方窗口提示（3） Rows Affeted）。当Set  NOCOUNT 为OFF时，返回计数，我们应该在存储过程的头部加上Set  NOCOUNT ON这样的话，在推出存储过程的时候加上Set Nocunt Off这样的话，以达到优化存储过程的目的。

二、事务内设置保存点

      用户可以在事务内设置保存点或标记。保存点定义如果有条件地取消事务的一部分，事务可以返回的位置。如果将事务回滚到保存点，则必须（如果需要，使用更多的Transact-SQL语句和Commite  Transaction语句）继续完成事务，或者必须（通过事务回滚到起始点）完全取消事务。若要取消整个事务，请使用Rollback Tansaction_name格式。这将撤销事务的所有语句和过程。如：

Create  Procedure  MyProcedure
AS
     Begin
             Set  NOCUNT  ON;
             Set   XACT_ABORT  ON;
             begin  tran  ok --开始一个事务OK 
                    delete  from  rxqz  where qz=rx015'  --删除数据
         save  tran  bcd  --保存一个事务点命名为bcd
                update  sz  set name='ln'  where name='l'  ----修改数据
         if  @@error<>0 --判断修改数据有没有出错 
              begin --如果出错           
                 rollback   tran  bcd  --回滚事务到BCD 的还原点               commit   tran  ok  --提交事务                   
              end             
           else  --没有出错           
             commit  tran ok --提交事务  
     End

说明：1、@@error判断是否有错误，为0表示没有错误，但是对那种重大的错误无法判断，而且@@error只能前一句SQL生效。
三、存储过程使用Try....catch捕获错误

     在存储过程中可以使用try....catch语句来捕获错误，如下：

 Create Procedure  MyProcedure   
 (  
  @Param1       nvarchar(10),     
  @param2    nvarchar(10)   
 )  
 AS    
   Begin    
       Set    NOCOUNT    ON;    
       Begin  try
              Delete from       table1 where name=’abc’;   
              Insert into       table2 values(value1,value2,value3);    
       End    try    
       Begin  Catch      
            SELECT ERROR_NUMBER()  AS  ErrorNumber,        
            ERROR_MESSAGE()  AS  ErrorMessage;     
        End    Catch    
  End

　　说明：1、捕获错误的函数有很多，如下：

           ERROR_NUMBER() 返回错误号。

　　　　ERROR_SEVERITY() 返回严重性。

　　　　ERROR_STATE() 返回错误状态号。

　　　　ERROR_PROCEDURE() 返回出现错误的存储过程或触发器的名称。

　　　　ERROR_LINE() 返回导致错误的例程中的行号。

　　　　ERROR_MESSAGE() 返回错误消息的完整文本。该文本可包括任何可替换参数所提供的值，如长度、对象名或时间。

　　　　2、有些错误，如sql语句中的表名称输入错误，这是数据库引擎无法解析这个表名称时，所发生的错误在当前的try…catch语句中无法捕获，必须由外层调用该存储过程的地方使用try…catch来进行捕获。

 

四、存储过程中事务和try…catch联合使用

　　在存储过程中使用事务时，如果没有try…catch语句，那么当set xact_abort on时，如果有错误发生，在批处理语句结束后，系统会自动回滚所有的sql操作。当set xact_abort off时，如果有错误发生，在批处理语句结束后，系统会执行所有没有发生错误的语句，发生错误的语句将不会被执行。

在存储过程中使用事务时，如果存在try…catch语句块，那么当捕获到错误时，需要在catch语句块中手动进行Rollback操作，否则系统会给客户端传递一条错误信息。如果在存储过程开始处将set xact_abort on，那么当有错误发生时，系统会将当前事务置为不可提交状态，即会将xact_state()置为-1，此时只可以对事务进行Rollback操作，不可进行提交(commit)操作，那么我们在catch语句块中就可以根据xact_state()的值来判断是否有事务处于不可提交状态，如果有则可以进行rollback操作了。如果在存储过程开始处将set xact_abort off，那么当有错误发生时，系统不会讲xact_state()置为-1，那么我们在catch块中就不可以根据该函数值来判断是否需要进行rollback了，但是我们可以根据@@Trancount全局变量来判断，如果在catch块中判断出@@Trancount数值大于0，代表还有未提交的事务，既然进入catch语句块了，那么还存在未提交的事务，该事务应该是需要rollback的，但是这种方法在某些情况下可能判断的不准确。推荐的方法还是将set xact_abort on，然后在catch中判断xact_state()的值来判断是否需要Rollback操作。

下面我们来看看两个例子：

一．使用Set xact_abort   on 

Create proc myProcedure
As
    begin
       set xact_abort on;
       begin try
           begin tran
              insert into TestStu values('Terry','boy',23);
              insert into TestStu values('Mary','girl',21);
           commit tran
       end try
       begin catch
           --在此可以使用xact_state()来判断是否有不可提交的事务,不可提交的事务
           --表示在事务内部发生错误了。Xact_state()有三种值：-1.事务不可提交；
           --1.事务可提交；0.表示没有事务，此时commit或者rollback会报错。
           if xact_state()=-1
              rollback tran;
       end catch
end

 二.使用Set xact_abort off 

Create  proc  myProcedure
As
    begin
       set xact_abort off;
       begin try
           begin tran
              insert into TestStu values('Terry','boy',23);
              insert into TestStu values('Mary','girl',21);
           commit tran
       end try
       begin catch
           --在此不可以使用xact_state来判断是否有不可提交的事务
           --只可以使用@@Trancount来判断是否有还未提交的事务，未提交的事务未必
           --就是不可提交的事务，所以使用@@TranCount>0后就RollBack是不准确的
           if @@TranCount>0
              rollback tran;
       end catch
end

      另外，对于@@Trancount需要说明的是，begin  tran 语句将 @@Trancount加 1。Rollback  tran将 @@Trancount递减到 0，但 Rollback tran savepoint_name 除外，它不影响 @@Trancount。Commit  tran 或 Commit  work 将 @@Trancount 递减 1。