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