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

CREATE PROCEDURE [dbo].********
(
	@smallOrderNo varchar(50),
	@phoneModel varchar(50),
	@beginSn varchar(50),			
	@endSn varchar(50)
)
AS 
	SET NOCOUNT ON

	declare @error int = 0				---事务中的错误记录,定义存储过程中的变量(类似程序中的变量设定)  
	declare @errerMsg varchar(500)		---事物中的错误信息记录
	declare @earlyImportDate datetime	---查找到的最早的包装数据
	declare @fiveDaysAgo datetime		---当前系统时间的5天前时间点

	create table #macSnTemp					--创建临时表
	(
		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)
	)

	begin								--插入临时表,用以数据操作
		insert into #macSnTemp
			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 (phoneModel=@phoneModel or @phoneModel='')  
				and (sn>=@beginSn or  @beginSn='') 
				and (sn<=@endSn or  @endSn='')
 
	end
  
	--设置事物回滚机制，xact_abort为 on，回滚整个事务
	set xact_abort on 
	--开启事务
	begin transaction 
		if not exists(select * from #macSnTemp)
			begin
				set @errerMsg='没有查询到订单数据！'    
				rollback transaction  
				select @errerMsg AS errorMsg
				return -1 --设置操作结果错误标识   
			end

		select top 1 @earlyImportDate = importDate from  #macSnTemp			--将最早的包装时间赋值
			where boxNo is not null
			order by importDate

		select @fiveDaysAgo =DateAdd(day,-5,getdate())				--系统5天前时间
	 
		if exists(select mac from #macSnTemp where (mac ='' or mac is null))
			begin 
				set @errerMsg='该订单信息内不含mac,非正常彩盒包装数据，无法删除！'    
				rollback transaction  
				select @errerMsg AS errorMsg
				return -1 --设置操作结果错误标识  
				  
			end 
		else if(ISNULL(@earlyImportDate,'1900-01-01 00:00:00.000')<@fiveDaysAgo)
			begin 
				set @errerMsg='该订单信息最早包装时间在5天之前,不允许清空订单包装数据！'    
				rollback transaction  
				select @errerMsg AS errorMsg
				return -1 --设置操作结果错误标识   
			end
		else
			begin
				-------------进行数据备份
				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 #macSnTemp
					
				set @error+=@@ERROR --记录有可能产生的错误号  	

				-------------备份完清空包装数据
				update macSnInfo set sn=null,boxNo=null,status=null,currentBoxNum=null,boxNumMax=null,
						sortBoxNum=null,importNum=null,importDate=null,exportDate=null,
						zpuz=null,rfpi=null,bigBatchNo=null,smallBatchNo=null
				where	(smallOrderNo=@smallOrderNo or @smallOrderNo='') 
						and (phoneModel=@phoneModel or @phoneModel='')  
						and (sn>=@beginSn or  @beginSn='') 
						and (sn<=@endSn or  @endSn='')

				set @error+=@@ERROR --记录有可能产生的错误号  
			end


if(@error<>0 or @errerMsg<>'')  
  begin  
    rollback transaction  
	select '-1' AS errorMsg
	delete from #macSnTemp;				--删除临时表
    return -1 --设置操作结果错误标识  
	
  end  
else  
  begin  
    commit transaction   
	select '1' AS errorMsg
	delete from #macSnTemp;				--删除临时表
    return 1 --操作成功的标识  
	
  end