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

CREATE procedure [dbo].*******(@smallOrderNo varchar(50),@phoneModel varchar(50),@beginBoxNo varchar(50),@endBoxNo varchar(50))ASSET NOCOUNT ONdeclare @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_Boxselect 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,manufacturerIdfrom IM_Boxwhere(phoneModel=@phoneModel or @phoneModel='') and (boxNo>=@beginBoxNo or @beginBoxNo='')  and (boxNo<=@endBoxNo or  @endBoxNo='') and (produceNo=@smallOrderNo or  @smallOrderNo='')insert into #IM_Box_detailselect b.mac,b.sn,b.boxNo,b.flag,b.packtime,b.rfpi from #IM_Box a inner join IM_Box_detail bon a.boxNo=b.boxNo end--设置事物回滚机制，xact_abort为 on，回滚整个事务set xact_abort on --开启事务begin transaction if not exists(select * from #IM_Box)beginset @errerMsg='没有查询到订单数据！'    rollback transaction  select @errerMsg AS errorMsgreturn -1 --设置操作结果错误标识   endelse if exists(select * from #IM_Box_detail where packtime is null or packtime='')beginset @errerMsg='该订单不存在包装时间，请确认是否2014年之前的数据！'    rollback transaction  select @errerMsg AS errorMsgreturn -1 --设置操作结果错误标识   end select top 1 @earlyImportDate = packtime from  #IM_Box_detail--将最早的包装时间赋值where boxNo is not nullorder 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 errorMsgreturn -1 --设置操作结果错误标识   end-------------------------上面的情况都没问题 开始备份并删除数据begin-------------进行数据备份insert into del_bak_IM_Boxselect 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_Boxinsert into del_bak_IM_Box_detailselect mac,sn,boxNo,flag,packtime,rfpi,getdate()   from #IM_Box_detail set @error+=@@ERROR --记录有可能产生的错误号  -----------备份完清空包装数据delete from IM_Boxwhere(phoneModel=@phoneModel or @phoneModel='') and (boxNo>=@beginBoxNo or @beginBoxNo='')  and (boxNo<=@endBoxNo or  @endBoxNo='') and (produceNo=@smallOrderNo or  @smallOrderNo='')delete from IM_Box_detailwhere 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_detailendif(@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