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
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
endbegin 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)
beginset @Rstatus = -1
set @Rcode = 0
set @Rmsg = '过程出错'
--回滚
rollback tran
--记录错误日志
exec dbo.CJ_ERRend
else
begin
if(-1 <> XACT_STATE())
beginset @Rstatus = -1
set @Rcode = 0
set @Rmsg = '子过程出错'
--回滚到还原点
rollback tran CJdemo1_P
--记录错误日志
exec dbo.CJ_ERR
end
end
end catch
end>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
图1：作为父过程执行
图2：作为子过程执行