SQLServer 存储过程 带事务处理实例（一）

ALTER procedure [dbo].[P_DelImportData](@orderNo varchar(50),--定义存储过程传入参数 @smallOrderNo varchar(50),@phoneModel varchar(50),@customer varchar(50))ASSET NOCOUNT ONdeclare @error int = 0    ---事务中的错误记录,定义存储过程中的变量(类似程序中的变量设定)  declare @errerMsg varchar(500)       ---事物中的错误信息记录declare @moveNo int       ---挪单的数量declare @originalOrderNo varchar(50) ---挪单的原大单号 create table #macSnInfo--创建临时表 (orderNo varchar(50),mac varchar(50),sn varchar(50),boxNo varchar(50),status varchar(50),currentBoxNum int,boxNumMax int,smallOrderNo varchar(50),sortBoxNum int,importNum int,importDate datetime,exportDate datetime,phoneModel varchar(50),zpuz varchar(50),rfpi varchar(50),bigBatchNo varchar(50),smallBatchNo varchar(50))create table #phoneInfo(orderNo varchar(50),customer varchar(50),smallOrderNo varchar(50),phoneModel varchar(50),newPower varchar(50),oldPower varchar(50),software varchar(50),hardware varchar(50),amount varchar(50))begin---将数据插入临时表做数据处理insert into #macSnInfoselect orderNo,mac,sn,boxNo,status,currentBoxNum,boxNumMax,smallOrderNo,sortBoxNum,importNum,importDate,exportDate,phoneModel,zpuz,rfpi,bigBatchNo,smallBatchNofrom macSnInfowhere(smallOrderNo=@smallOrderNo or @smallOrderNo='') and (orderNo=@orderNo or  @orderNo='')and (phoneModel=@phoneModel or @phoneModel='') insert into #phoneInfoselect orderNo,customer,smallOrderNo,phoneModel,newPower,oldPower,software,hardware,amount from phoneInfowhere(smallOrderNo=@smallOrderNo or @smallOrderNo='') and (orderNo=@orderNo or  @orderNo='')and (phoneModel=@phoneModel or @phoneModel='')and (customer=@customer or @customer='')  end--设置事物回滚机制，xact_abort为 on，回滚整个事务set xact_abort on --开启事务begin transaction if not exists(select * from #phoneInfo)beginset @errerMsg='没有查询到订单数据！'    rollback transaction  select @errerMsg AS errorMsgreturn -1 --设置操作结果错误标识   endelse if exists(select boxNo from #macSnInfo where boxNo is not null)--如果包装表查询出的结果是已经包装的beginset @errerMsg='该订单已经包装过，不能直接删除，请先清空包装信息！'rollback transaction  select @errerMsg AS errorMsgreturn -1 --设置操作结果错误标识     endif exists(select * from #phoneInfo where orderNo=smallOrderNo)--如果存在订单号相同的phoneInfo，是正常单begin insert into del_bak_phoneInfo--先进行数据备份select orderNo,customer,smallOrderNo,phoneModel,newPower,oldPower,software,hardware,amount,getdate()  from #phoneInfo set @error+=@@ERROR --记录有可能产生的错误号  insert into del_bak_macSnInfo select orderNo,mac,sn,boxNo,status,currentBoxNum,boxNumMax,smallOrderNo,sortBoxNum,importNum,importDate,exportDate,phoneModel,zpuz,rfpi,bigBatchNo,smallBatchNo,getdate()  from #macSnInfoset @error+=@@ERROR --记录有可能产生的错误号  ---------------- 备份完数据开始删除delete from phoneInfowhere(smallOrderNo=@smallOrderNo or @smallOrderNo='') and (orderNo=@orderNo or  @orderNo='')and (phoneModel=@phoneModel or @phoneModel='')and (customer=@customer or @customer='')  set @error+=@@ERROR --记录有可能产生的错误号 delete from macSnInfowhere(smallOrderNo=@smallOrderNo or @smallOrderNo='') and (orderNo=@orderNo or  @orderNo='')and (phoneModel=@phoneModel or @phoneModel='')  set @error+=@@ERROR --记录有可能产生的错误号 endelse--不存在订单号相同的phoneInfo，是挪单begininsert into del_bak_phoneInfo--先进行数据备份select orderNo,customer,smallOrderNo,phoneModel,newPower,oldPower,software,hardware,amount,getdate() from #phoneInfoset @error+=@@ERROR --记录有可能产生的错误号  insert into del_bak_macSnInfo select orderNo,mac,sn,boxNo,status,currentBoxNum,boxNumMax,smallOrderNo,sortBoxNum,importNum,importDate,exportDate,phoneModel,zpuz,rfpi,bigBatchNo,smallBatchNo,getdate()  from #macSnInfoset @error+=@@ERROR --记录有可能产生的错误号  ---------------- 挪单删除要先还原phoneInfo数量，再删除select @moveNo = ISNULL(amount,0) from #phoneInfo--记录挪单的数量select @originalOrderNo = orderNo from #phoneInfo--记录原大单号(挪单前)update phoneInfo set amount = amount+@moveNowheresmallOrderNo=@originalOrderNoand orderNo=@originalOrderNoand (phoneModel=@phoneModel or @phoneModel='')and (customer=@customer or @customer='')  set @error+=@@ERROR --记录有可能产生的错误号  delete from phoneInfo --删除挪单记录where(smallOrderNo=@smallOrderNo or @smallOrderNo='') and (orderNo=@orderNo or  @orderNo='')and (phoneModel=@phoneModel or @phoneModel='')and (customer=@customer or @customer='')  set @error+=@@ERROR --记录有可能产生的错误号 update macSnInfo set smallOrderNo = @originalOrderNo --恢复挪单前的小单号(原大单号)where(smallOrderNo=@smallOrderNo or @smallOrderNo='') and (orderNo=@orderNo or  @orderNo='')and (phoneModel=@phoneModel or @phoneModel='')  set @error+=@@ERROR --记录有可能产生的错误号   end  if(@error<>0 or @errerMsg<>'')    begin      rollback transaction  select '-1' AS errorMsg    return -1 --设置操作结果错误标识    end  else    begin      commit transaction   select '1' AS errorMsg    return 1 --操作成功的标识    end