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

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,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  

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!