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