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

概述CREATE procedure [dbo].*******( @smallOrderNo varchar(50), @phoneModel varchar(50), @beginBoxNo varchar(50), @endBoxNo varchar(50))AS SET NOCOUNT ON declare @error int = 0 ---事务中的错误记录,

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		endif(@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  

总结

以上是内存溢出为你收集整理的SQLServer 存储过程 带事务处理实例（四）全部内容，希望文章能够帮你解决SQLServer 存储过程 带事务处理实例（四）所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。