sqlserver 中复杂存储过程回滚

解决办法一:
       在begin transaction 前面加set xact_abort on
       这样就可以在过程出错时中断执行，当然也就不能commit了
       如此再在最后一个操作的后面加入 if @error=0 就说明所有的操作都执行成功了
       就可以 commit
       具体如:
           set xact_abort on
           begin trans
           declare ...
           <1>操作
           <2>操作
           ...
           <n>操作
           if @error<>0
              begin
                rollback transaction
              end
           else
              begin
                commit transaction
              end
           end
 解决办法二:
       不加 set xact_abort on 而是声明一个记录错误的变量
          比如：
           begin trans
           declare @nror int
           set @nror=0
           <1>操作
           set @nror=@nror+@@error
           <2>操作
           set @nror=@nror+@@error
           ...
           <n>操作
           set @nror=@nror+@@error
           if @ror<>0
              begin
                rollback transaction
              end
           else
              begin
                commit transaction
              end
           end
       显然这样在每一个操作的背后都要加入一个记录错误的操作
       最后再判断错误是不是0，如果不是则说明过程中某一步出了错，就不commit了。
       这比第一种方法在烦一点，

CREATE PROCEDURE order_down_forjoin2

@result varchar(50)  output,
@orderno varchar(50),
@out_trade_no varchar(50),
@product_class varchar(50),
@brand varchar(50),
@product_no varchar(30),
@product_size varchar(50),
@color varchar(100),
@color2 varchar(100),
@number int,
@price money,
@area varchar(50),
@city varchar(50),
@county varchar(50),
@address varchar(200),
@zip varchar(20),
@username varchar(50),
@phone varchar(50),
@mobile varchar(50),
@email varchar(100),
@joinno varchar(50),
@isems int,
@ems money,
@send varchar(400),
@memo text,
@sendinfo1 varchar(50),
@sendinfo1time datetime,
@sendinfo varchar(50),

/*库存的更新*/
@number55 int,
@number2 int,
@number3 int,
@number4 int,
@number5 int,

/*库存记录的插入*/
@number222 int,
@stock varchar(50),
@operation_type varchar(50),
@operation varchar(50),
@memo_product_inout_record text,
@users varchar(50),

/*备用金的更新*/
@checks varchar(16),
@lastprice money,
@memo_protect_log text,

@server varchar(50),
@disprice_class varchar(50),
@disprice money,
@price2 money


 AS
begin TRANSACTION
           declare @nror int
           set @nror=0        

insert into tbl_order 
(orderno,out_trade_no,product_class,brand,product_no,product_size,color,color2,price,area,city,county,server,address,zip,username,phone,email,memo,joinno,isems,ems,send,sendinfo1,sendinfo1time,sendinfo,mobile,disprice,disprice_class)
values
(@orderno,@out_trade_no,@product_class,@brand,@product_no,@product_size,@color,@color2,@price,@area,@city,@county,@server,
@address,@zip,@username,@phone,@email,@memo,@joinno,@isems,@ems,@send,@sendinfo1,@sendinfo1time,@sendinfo,@mobile,@disprice,@disprice_class)       
  set @nror=@nror+@@error

update tbl_product_store  set  number=number-@number55,number2=number2-@number2,number3=number3-@number3,number4=number4-@number4,number5=number5-@number5  
where product_no=@product_no and color=@color and product_size=@product_size     
  set @nror=@nror+@@error

insert into tbl_product_inout_record
(product_no,color,product_size,number2,stock,users,operation_type,operation,memo)
 values (@product_no,@color,@product_size,0-
@number,@stock,@users,@operation_type,@operation,@memo_product_inout_record)    
  set @nror=@nror+@@error

update tbl_protect set price=@lastprice ,checks=@checks    where joinno=@joinno
 set @nror=@nror+@@error

insert into tbl_protect_log (joinno,orderno,price,product_no,memo) values (@joinno,@orderno,0-@price2,@product_no+'-'+@color+'-'+@product_size,@memo_protect_log)
 set @nror=@nror+@@error


           if @nror<>0
         
              begin
  set @result='失败'
                rollback transaction
              end
           else
        
              begin
  set @result='成功'
                commit transaction
              end
GO