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

ALTER procedure [dbo].[P_DelImportData]
(
@orderNo varchar(50),--定义存储过程传入参数 
@smallOrderNo varchar(50),
@phoneModel varchar(50),
@customer varchar(50)
)
AS
SET NOCOUNT ON

declare @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 #macSnInfo
select orderNo,mac,sn,boxNo,status,currentBoxNum,boxNumMax,smallOrderNo
,sortBoxNum,importNum,importDate,exportDate,phoneModel,zpuz,
rfpi,bigBatchNo,smallBatchNo
from macSnInfo
where(smallOrderNo=@smallOrderNo or @smallOrderNo='') 
and (orderNo=@orderNo or  @orderNo='')
and (phoneModel=@phoneModel or @phoneModel='') 

insert into #phoneInfo
select orderNo,customer,smallOrderNo,phoneModel,newPower,oldPower
,software,hardware,amount 
from phoneInfo
where(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)
begin
set @errerMsg='没有查询到订单数据！'    
rollback transaction  
select @errerMsg AS errorMsg
return -1 --设置操作结果错误标识   
end
else if exists(select boxNo from #macSnInfo where boxNo is not null)
--如果包装表查询出的结果是已经包装的
begin
set @errerMsg='该订单已经包装过，不能直接删除，请先清空包装信息！'
rollback transaction  
select @errerMsg AS errorMsg
return -1 --设置操作结果错误标识     
end

if 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 #macSnInfo

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 --记录有可能产生的错误号 

delete from macSnInfo
where(smallOrderNo=@smallOrderNo or @smallOrderNo='') 
and (orderNo=@orderNo or  @orderNo='')
and (phoneModel=@phoneModel or @phoneModel='')  
set @error+=@@ERROR --记录有可能产生的错误号 

end
else
--不存在订单号相同的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 #macSnInfo

set @error+=@@ERROR --记录有可能产生的错误号  

---------------- 挪单删除要先还原phoneInfo数量，再删除

select @moveNo = ISNULL(amount,0) from #phoneInfo--记录挪单的数量
select @originalOrderNo = orderNo from #phoneInfo--记录原大单号(挪单前)
update phoneInfo set amount = amount+@moveNo
wheresmallOrderNo=@originalOrderNo
and orderNo=@originalOrderNo
and (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