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

2017-03-07 14:10
CREATE PROCEDURE [dbo].********
(
	@sn varchar(50)
)
AS
	SET NOCOUNT ON
	Declare
		@parentSn varchar(20),		--父SN,VCP41
		@childSn1 varchar(20),		--子SN1,CPE80
		@childSn2 varchar(20),		--子SN2,CPE80
		@error int = 0,				---事务中的错误记录,定义存储过程中的变量(类似程序中的变量设定)  
		@errerMsg varchar(500)		---事物中的错误信息记录

	CREATE TABLE #t_binding_relation		--创建绑定关系临时表
	(
		parent varchar(50),
		child varchar(50),
		smallOrderNumber varchar(80), 
	)
	
	begin			---将全部数据放入临时表,便于后面的数据操作而不锁表
		insert into #t_binding_relation 
			select parent,child,smallOrderNumber from t_binding_relation 
	end
	 
	--设置事物回滚机制,xact_abort为 on,回滚整个事务
	set xact_abort on 
	--开启事务
	begin transaction 
	 ---------------------------------输入的SN是parentSn------------------- 
		IF exists(select 1 from #t_binding_relation where parent=@sn)	
			begin
			 
				select @parentSn=@sn		--赋值parentSN
				select top 1 @childSn1=child from #t_binding_relation where parent= @parentSn					--拆分出两个childSn
				select @childSn2=child from #t_binding_relation where parent= @parentSn and child <>@childSn1
			end
		---------------------------------输入的SN是childSN-----------------------
		ELSE  IF exists(select 1 from t_binding_relation where child=@sn)
			begin
				select @childSn1 = @sn		--赋值childSN1 
				select top 1 @parentSn=parent from #t_binding_relation where child= @childSn1
				select @childSn2 = child from #t_binding_relation where parent=@parentSn and child<>@childSn1 

			end
		else
			begin 
				set @errerMsg='该SN不存绑定关系,请确认!'    
				rollback transaction  
				select @errerMsg AS errorMsg
				return -1 --设置操作结果错误标识   
			end

		--------------------------------开始备份,删除数据-------------------------------------- 
		BEGIN
			insert into del_bak_binding_relation 
				select parent,child,smallOrderNumber,getdate()   
					from #t_binding_relation 
				where parent=@parentSn and child in(@childSn1,@childSn2)

			------备份完数据删除正式表的数据
			delete from t_binding_relation
				where parent=@parentSn and child in(@childSn1,@childSn2)

			---删除临时表
			delete from #t_binding_relation
		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