set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER proc [dbo].[ElecWallet]
---------------电子钱包缴费

@userid int, ------- 用户ID
@usermoney money, -------用户支付金额
@username VarChar(50),-------用户名
@ordtime DateTime, ------订单时间
@inmoney Money, -------进钱
@outmoney Money, ------出钱
@info VarChar(255),  ------备注
@ordid VarChar(50), ---------商户接口订单号
@scrmoney varchar(255), -------加密余额
@key varchar(50),                 ---------私钥

@CName varchar(50),----所在城市
@jf_haoma varchar(50),----缴费号码
@FailType varchar(50),---失败记录
@z_is19pay int,----充值渠道
@Dealstatus int,-----处理状态
@randkey int,----- 随机数
@jf_order varchar(50),-----订单号
@jf_jinjia money,-----进货价
@z_Sell money,----用户需支付金额
@jf_name varchar(50), ------移动，电信，连通
@jf_beizhu varchar(1000), --------缴费表备注
@zhuangtai int,  ----------状态

@check int output ----输出运行状态
-----@surmmoney money output -------输出余额，用于程序加密
AS

begin transaction
declare @tbUserMoney money ----tbuser表中usermoney
declare @SurMoney money ----usermoney表中usermoney
declare @Mmoney varchar ------tbuser表中加密mmoney
declare @InsertMoney money ---------最终插入金额usermoney
declare @i int
declare @md5money varchar
declare @VarMmoney varchar
declare @newstr varchar
declare @errorSum int    --定义变量，用于累计事务执行过程中的错误

             set @errorSum =0
             select @tbUserMoney = usermoney, @Mmoney = mmoney from tbuser where userid = @userid    ---------获取用户账户余额
             --select top 1 @SurMoney =  usermoney from usermoney where userid =@userid order by id desc   ---------获取用户电子钱包余额
             if @tbusermoney < @z_Sell
                begin
                  --  set @check = 2   ---------余额不足
                    set @errorSum=@errorSum+@@error
                   
                end
                    else
                   begin                 
                      set @InsertMoney = @tbUserMoney - @z_Sell
                    ------------------先扣费，在下明细定单--------------------------------
                    update tbuser set usermoney = @InsertMoney where userid =@userid ----------扣除usermoney账户余额
                    set @errorSum=@errorSum+@@error
                   insert into  usermoney(username,userid,ordid,ordtime,inmoney,outmoney,usermoney,info )  values(@username,@userid ,@ordid,@ordtime,@inmoney,@z_Sell,@InsertMoney,@info) -----插入明细数据
                   set @errorSum=@errorSum+@@error
                   -------再次进行比对
                   select @tbUserMoney = usermoney, @Mmoney = mmoney from tbuser where userid = @userid    ---------获取最新用户账户余额
                  -- select top 1 @SurMoney =  usermoney from usermoney where userid =@userid order by id desc   ---------获取最新用户电子钱包余额
                    --if @tbUserMoney <> @SurMoney    -------不一致则回滚
                  if @tbUserMoney <> @tbUserMoney
                   begin
                      set @check =5  ---------数据处理中错误(包括数据先后处理差错和数据被篡改)        
                      set @errorSum=@errorSum+@@error
                   end
                  else
                   begin           ----------------------加密 私钥和余额
                          set @varmmoney =  convert(varchar(100),@InsertMoney) -----余额转成字符串
                           
                                   while(@i <len(@varmmoney))
                                   begin
                                          set @newstr = @newstr + substring(@varmmoney,@i,1)
                                              if @i > len(@key)-1
                                              begin
                                              set  @newstr = @newstr + Substring(@key,@i - Len(@key), 1)
                                              end
                                          else
                                             begin
                                             set  @newstr = @newstr + Substring(@key,@i, 1)
                                             end
                                             set @i=@i+1
                                    end
          ---@newstr还需要一次MD5加密，拷贝到SQL2005上时  set @newstr  =   SUBSTRING(sys.fn_VarBinToHexStr(hashbytes('MD5', @newstr)),3,32)  -----SQL2000不支持hashbytes的加密函数 
                                   set @newstr = substring(sys.fn_varbintohexstr(hashbytes('MD5', @newstr)),3,32)
                                   update tbuser set mmoney = @newstr where userid =@userid   ---------------更新加密金额
                                    set @errorSum=@errorSum+@@error
                                     insert into  NewJiaoFei (CName,jf_fee,jf_name,userid,jf_haoma,jf_beizhu,zhuangtai,jf_order,jf_jinjia,jf_paydate,jf_AddTime,z_Sell,z_is19pay,Dealstatus,FailType,RandKey,Jf_merchantorder)
                                     values
                                                             (@CName,@usermoney,@jf_name,@userid,@jf_haoma,@jf_beizhu,@zhuangtai,@jf_order,@jf_jinjia,@ordtime,@ordtime,@z_Sell,@z_is19pay,@Dealstatus,@FailType,@randkey,@ordid) -----添加数据未完成
                                   set @errorSum=@errorSum+@@error
                                   --set @check = 0   ---------------成功扣款
                   end
end

if @errorSum>0
    begin
        set @check = 1
        rollback transaction
    end
else
    begin
      set @check = 2
        commit transaction
    end

此条存过 我在功能上已经通过了，没有问题

 关键是最后这个回滚的问题，我输入错误数据进去 @@error输出出来是0

   但是 错误数据那个代码 确实没有执行

     小弟不知道怎么用这个错误回滚的机制，希望高手指点下