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

CREATE procedure [dbo].*******
(
@smallOrderNo varchar(50),
@phoneModel varchar(50),
@beginBoxNo varchar(50),
@endBoxNo varchar(50)
)
AS
SET NOCOUNT ON

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

create table #IM_Box--创建外箱包装信息临时表
(
printLanguage int,
orderNumber varchar(80),
produceNo varchar(50),
phoneModel varchar(50),
printModel varchar(50),
beginBoxNo varchar(50),
endBoxNo varchar(50),
boxNo varchar(50),
barcodePrinterModel varchar(50),
softwareVersion varchar(50),
hardwareVersion varchar(50),
packingQuantity int,
total int,
grossWeight float,
netWeight float,
producePattern int,
plugInformation varchar(50),
manufacturerName varchar(100),
lineNumber varchar(50),
customer varchar(100),
shippingArea varchar(50),
powerPlugType varchar(50),
newPower varchar(50),
fullOrNotFlag bit,
factoryName varchar(100),
insertOK bit,
instruction varchar(50),
disks varchar(50),
handle varchar(50),
chanxian varchar(50),
standardId int,
productId int,
manufacturerId int 
)

create table #IM_Box_detail--创建外箱包装明细信息临时表
(
mac varchar(50),
sn varchar(50),
boxNo varchar(50),
flag int,
packtime datetime,
rfpi varchar(50)
)

begin---将查询到的数据放入临时表，便于后面的数据操作而不锁表
insert into #IM_Box
select printLanguage,orderNumber,produceNo,phoneModel,printModel,beginBoxNo,endBoxNo,boxNo
,barcodePrinterModel,softwareVersion,hardwareVersion,packingQuantity,total,grossWeight
,netWeight,producePattern,plugInformation,manufacturerName,lineNumber,customer
,shippingArea,powerPlugType,newPower,fullOrNotFlag,factoryName,insertOK
,instruction,disks,handle,chanxian,standardId,productId,manufacturerId
from IM_Box
where(phoneModel=@phoneModel or @phoneModel='') 
and (boxNo>=@beginBoxNo or @beginBoxNo='')  
and (boxNo<=@endBoxNo or  @endBoxNo='') 
and (produceNo=@smallOrderNo or  @smallOrderNo='')


insert into #IM_Box_detail
select b.mac,b.sn,b.boxNo,b.flag,b.packtime,b.rfpi 
from #IM_Box a inner join IM_Box_detail b
on a.boxNo=b.boxNo

end

--设置事物回滚机制，xact_abort为 on，回滚整个事务
set xact_abort on 
--开启事务
begin transaction 
if not exists(select * from #IM_Box)
begin
set @errerMsg='没有查询到订单数据！'    
rollback transaction  
select @errerMsg AS errorMsg
return -1 --设置操作结果错误标识   
end
else if exists(select * from #IM_Box_detail where packtime is null or packtime='')
begin
set @errerMsg='该订单不存在包装时间，请确认是否2014年之前的数据！'    
rollback transaction  
select @errerMsg AS errorMsg
return -1 --设置操作结果错误标识   
end

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

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

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

-------------------------上面的情况都没问题 开始备份并删除数据
begin
-------------进行数据备份
insert into del_bak_IM_Box
select printLanguage,orderNumber,produceNo,phoneModel,printModel,beginBoxNo,endBoxNo,boxNo
,barcodePrinterModel,softwareVersion,hardwareVersion,packingQuantity,total,grossWeight
,netWeight,producePattern,plugInformation,manufacturerName,lineNumber,customer
,shippingArea,powerPlugType,newPower,fullOrNotFlag,factoryName,insertOK
,instruction,disks,handle,chanxian,standardId,productId,manufacturerId,getdate()  
from #IM_Box

insert into del_bak_IM_Box_detail
select mac,sn,boxNo,flag,packtime,rfpi,getdate()   
from #IM_Box_detail

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

-----------备份完清空包装数据
delete from IM_Box
where(phoneModel=@phoneModel or @phoneModel='') 
and (boxNo>=@beginBoxNo or @beginBoxNo='')  
and (boxNo<=@endBoxNo or  @endBoxNo='') 
and (produceNo=@smallOrderNo or  @smallOrderNo='')
delete from IM_Box_detail
where boxNo in (select boxNo from #IM_Box)

--select * from  IM_Box
--where(phoneModel=@phoneModel or @phoneModel='') 
--and (boxNo>=@beginBoxNo or @beginBoxNo='')  
--and (boxNo<=@endBoxNo or  @endBoxNo='') 
--and (produceNo=@smallOrderNo or  @smallOrderNo='')
--select * from IM_Box_detail
--where boxNo in (select boxNo from #IM_Box)

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

delete from #IM_Box--删除临时表
delete from #IM_Box_detail
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