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

CREATE PROCEDURE [dbo].********
(
@smallOrderNo varchar(50),
@phoneModel varchar(50),
@beginSn varchar(50),
@endSn varchar(50)
)
AS 
SET NOCOUNT ON

declare @error int = 0---事务中的错误记录,定义存储过程中的变量(类似程序中的变量设定)  
declare @errerMsg varchar(500)---事物中的错误信息记录
declare @earlyImportDate datetime---查找到的最早的包装数据
declare @fiveDaysAgo datetime---当前系统时间的5天前时间点

create table #macSnTemp--创建临时表
(
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)
)

begin--插入临时表,用以数据操作
insert into #macSnTemp
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 (phoneModel=@phoneModel or @phoneModel='')  
and (sn>=@beginSn or  @beginSn='') 
and (sn<=@endSn or  @endSn='')

end

--设置事物回滚机制，xact_abort为 on，回滚整个事务
set xact_abort on 
--开启事务
begin transaction 
if not exists(select * from #macSnTemp)
begin
set @errerMsg='没有查询到订单数据！'    
rollback transaction  
select @errerMsg AS errorMsg
return -1 --设置操作结果错误标识   
end

select top 1 @earlyImportDate = importDate from  #macSnTemp--将最早的包装时间赋值
where boxNo is not null
order by importDate

select @fiveDaysAgo =DateAdd(day,-5,getdate())--系统5天前时间

if exists(select mac from #macSnTemp where (mac ='' or mac is null))
begin 
set @errerMsg='该订单信息内不含mac,非正常彩盒包装数据，无法删除！'    
rollback transaction  
select @errerMsg AS errorMsg
return -1 --设置操作结果错误标识  

end 
else if(ISNULL(@earlyImportDate,'1900-01-01 00:00:00.000')<@fiveDaysAgo)
begin 
set @errerMsg='该订单信息最早包装时间在5天之前,不允许清空订单包装数据！'    
rollback transaction  
select @errerMsg AS errorMsg
return -1 --设置操作结果错误标识   
end
else
begin
-------------进行数据备份
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 #macSnTemp

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

-------------备份完清空包装数据
update macSnInfo set sn=null,boxNo=null,status=null,currentBoxNum=null,boxNumMax=null,
sortBoxNum=null,importNum=null,importDate=null,exportDate=null,
zpuz=null,rfpi=null,bigBatchNo=null,smallBatchNo=null
where(smallOrderNo=@smallOrderNo or @smallOrderNo='') 
and (phoneModel=@phoneModel or @phoneModel='')  
and (sn>=@beginSn or  @beginSn='') 
and (sn<=@endSn or  @endSn='')

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


if(@error<>0 or @errerMsg<>'')  
  begin  
    rollback transaction  
select '-1' AS errorMsg
delete from #macSnTemp;--删除临时表
    return -1 --设置操作结果错误标识  

  end  
else  
  begin  
    commit transaction   
select '1' AS errorMsg
delete from #macSnTemp;--删除临时表
    return 1 --操作成功的标识  

  end