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

ALTER PRocedure [dbo].[P_DelImportData](	@orderNo varchar(50),			--定义存储过程传入参数 	@smallOrderNo varchar(50),	@phoneModel varchar(50),	@customer varchar(50))AS	SET NOCOUNT ON	declare @error int = 0    ---事务中的错误记录,定义存储过程中的变量(类似程序中的变量设定)  	declare @errerMsg varchar(500)       ---事物中的错误信息记录	declare @moveNo int       ---挪单的数量	declare @originalOrderNo varchar(50) ---挪单的原大单号								 	create table #macSnInfo				--创建临时表 	(		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)	)	create table #phoneInfo	(		orderNo varchar(50),		customer varchar(50),		smallOrderNo varchar(50),		phoneModel varchar(50),		newPower varchar(50),		oldPower varchar(50),		software varchar(50),		hardware varchar(50),		amount varchar(50)	)	begin						---将数据插入临时表做数据处理		insert into #macSnInfo			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 (orderNo=@orderNo or  @orderNo='')				and (phoneModel=@phoneModel or @phoneModel='') 		insert into #phoneInfo			select orderNo,customer,smallOrderNo,phoneModel,newPower,oldPower					,software,hardware,amount 					from phoneInfo			where	(smallOrderNo=@smallOrderNo or @smallOrderNo='') 				and (orderNo=@orderNo or  @orderNo='')				and (phoneModel=@phoneModel or @phoneModel='')				and (customer=@customer or @customer=''	)  				end	--设置事物回滚机制，xact_abort为 on，回滚整个事务	set xact_abort on 	--开启事务	begin transaction 		if not exists(select * from #phoneInfo)			begin				set @errerMsg='没有查询到订单数据！'    				rollback transaction  				select @errerMsg AS errorMsg				return -1 --设置操作结果错误标识   			end		else if exists(select boxNo from #macSnInfo where boxNo is not null)				--如果包装表查询出的结果是已经包装的			begin				set @errerMsg='该订单已经包装过，不能直接删除，请先清空包装信息！'				rollback transaction  				select @errerMsg AS errorMsg				return -1 --设置操作结果错误标识     			end		if exists(select * from #phoneInfo where orderNo=smallOrderNo)				--如果存在订单号相同的phoneInfo，是正常单			begin 				insert into del_bak_phoneInfo				--先进行数据备份					select orderNo,customer,smallOrderNo,phoneModel,newPower,oldPower							,software,hardware,amount,getdate()  							from #phoneInfo							 				set @error+=@@ERROR --记录有可能产生的错误号  								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 #macSnInfo									set @error+=@@ERROR --记录有可能产生的错误号  					---------------- 备份完数据开始删除				delete from phoneInfo					where	(smallOrderNo=@smallOrderNo or @smallOrderNo='') 						and (orderNo=@orderNo or  @orderNo='')						and (phoneModel=@phoneModel or @phoneModel='')						and (customer=@customer or @customer=''	)  				set @error+=@@ERROR --记录有可能产生的错误号 				delete from macSnInfo					where	(smallOrderNo=@smallOrderNo or @smallOrderNo='') 						and (orderNo=@orderNo or  @orderNo='')						and (phoneModel=@phoneModel or @phoneModel='')  				set @error+=@@ERROR --记录有可能产生的错误号 			end		else				--不存在订单号相同的phoneInfo，是挪单					begin					insert into del_bak_phoneInfo				--先进行数据备份					select orderNo,customer,smallOrderNo,phoneModel,newPower,oldPower							,software,hardware,amount,getdate() 							from #phoneInfo									set @error+=@@ERROR --记录有可能产生的错误号  				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 #macSnInfo				set @error+=@@ERROR --记录有可能产生的错误号  								---------------- 挪单删除要先还原phoneInfo数量，再删除				select @moveNo = ISNULL(amount,0) from #phoneInfo			--记录挪单的数量				select @originalOrderNo = orderNo from #phoneInfo			--记录原大单号(挪单前)				update phoneInfo set amount = amount+@moveNo					where	smallOrderNo=@originalOrderNo						and orderNo=@originalOrderNo						and (phoneModel=@phoneModel or @phoneModel='')						and (customer=@customer or @customer=''	)  				set @error+=@@ERROR --记录有可能产生的错误号  				delete from phoneInfo										 --删除挪单记录					where	(smallOrderNo=@smallOrderNo or @smallOrderNo='') 						and (orderNo=@orderNo or  @orderNo='')						and (phoneModel=@phoneModel or @phoneModel='')						and (customer=@customer or @customer=''	)  				set @error+=@@ERROR --记录有可能产生的错误号 				update macSnInfo set smallOrderNo = @originalOrderNo		 --恢复挪单前的小单号(原大单号)					where	(smallOrderNo=@smallOrderNo or @smallOrderNo='') 						and (orderNo=@orderNo or  @orderNo='')						and (phoneModel=@phoneModel or @phoneModel='')  									set @error+=@@ERROR --记录有可能产生的错误号 				  			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