SQL-SERVER2008 存储过程-子过程回滚

USE [******]
GO
/****** Object:  StoredProcedure [dbo].[CJdemo]    Script Date: 07/31/2017 17:47:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: ( Jetaime )
-- Create date:      ( 2017-07-29 11:17 )
-- Description: ( 过程DEMO1 )
-- =============================================
ALTER PROCEDURE [dbo].[CJdemo] 
@Rstatus INT OUTPUT,--返回标识号 0 成功 
@Rcode INT OUTPUT,--返回错误编码
@Rmsg VARCHAR(128) OUTPUT--返回描述状态说明标识
AS
declare @tran_counts  int

begin

--保存执行当前事务计数

set @tran_counts = @@TRANCOUNT

if(@tran_counts > 0)

begin

--作为子过程，设置会滚点

save tran CJdemo1_P

end

else

begin

--作为主过程运行,开始事务

begin tran

end

begin try

--正确执行语句

insert into parkErr (proName,proErr,proTime) values('1','123',GETDATE())

--错误执行语句

insert into parkErr (proName,proErr,proTime) values('2','456','1234')

if(0 = @tran_counts)

begin

set @Rstatus = 0

set @Rcode = 0

set @Rmsg = '成功返回'

--事务提交

commit tran

end

end try

begin catch

if(0 = @tran_counts)

begin

set @Rstatus = -1

set @Rcode = 0

set @Rmsg = '过程出错'

--回滚

rollback tran

--记录错误日志

exec dbo.CJ_ERR 

end

else

begin

if(-1 <> XACT_STATE())

begin

set @Rstatus = -1

set @Rcode = 0

set @Rmsg = '子过程出错'

--回滚到还原点

rollback tran CJdemo1_P

--记录错误日志

exec dbo.CJ_ERR

end

end

end catch

end

 

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

图1：作为父过程执行

 

图2：作为子过程执行