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

3/7/2017来源:SQL技巧人气:1836

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