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

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：作为子过程执行