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),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,manufacturerId,getdate()  
					from #IM_Box
			
			insert into del_bak_IM_Box_detail
				select mac,sn,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  

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!