[Stored Proc 1]
BEGIN
BEGIN TRANSACTION
...
exec sp 2
COMMIT
END
SP2的工作可以被回滚而不会丢失SP1的工作。但要实现这一点，您必须按照异常处理和嵌套事务中描述的非常具体的模式编写存储过程：
create procedure [usp_my_procedure_name]
as
begin
set nocount on;
declare @trancount int;
set @trancount = @@trancount;
begin try
if @trancount = 0
begin transaction
else
save transaction usp_my_procedure_name;
-- Do the actual work here
lbexit:
if @trancount = 0
commit;
end try
begin catch
declare @error int, @message varchar(4000), @xstate int;
select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
if @xstate = -1
rollback;
if @xstate = 1 and @trancount = 0
rollback
if @xstate = 1 and @trancount > 0
rollback transaction usp_my_procedure_name;
raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
end catch
end
并非所有错误都是可恢复的，有一些错误条件是一个事务无法从中恢复的，最明显的例子是死锁（在事务已经回滚后，你会收到死锁异常通知）。SP1和SP@都必须使用这个模式编写。如果你有一个不受控制的SP，或者想要简单地利用现有的存储过程来任意发出ROLLBACK语句，那么你的目的就失败了。
在SQL Server中没有自主事务。你可能会看到@@TRANCOUNT超过1，但是回滚会影响整个事务。
编辑要求提供文档。不知道有没有明确记录这一点的文档，但我可以向您展示它的实际应用。
USE tempdb;
GO
内部过程：
CREATE PROCEDURE dbo.sp2
@trip BIT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
PRINT @@TRANCOUNT;
IF @trip = 1
BEGIN
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END
PRINT @@TRANCOUNT;
END
GO
外部进程：
CREATE PROCEDURE dbo.sp1
@trip BIT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
PRINT @@TRANCOUNT;
BEGIN TRY
EXEC dbo.sp2 @trip = @trip;
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH
PRINT @@TRANCOUNT;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
PRINT @@TRANCOUNT;
END
GO
现在，让我们调用它并提交所有内容：
EXEC dbo.sp1 @trip = 0;
结果：
1
2
1
1
0
现在让我们调用它并回滚内部过程：
EXEC dbo.sp1 @trip = 1;
结果：
1
2
0 <-- 注意这里的回滚同时回滚了两个
执行后事务计数表明BEGIN和COMMIT语句不匹配。之前的计数=1，当前的计数=0。
0
0
每个存储过程必须以与其进入时相同的事务计数结束。如果不匹配，SQL Server 将发出错误 266，“执行后的事务计数表明缺少 COMMIT 或 ROLLBACK TRANSACTION 语句。”
如果一个存储过程没有启动最外层事务，则不应发出 ROLLBACK。
如果嵌套的存储过程开始一个新事务；但如果它检测到需要回滚且 @@TRANSACTION 值大于 1，则会引发错误并通过输出参数或返回值将错误消息返回给调用者，并发出 COMMIT 而不是 ROLLBACK。
CREATE PROCEDURE [dbo].[Pinner]
-- Add the parameters for the stored procedure here
@ErrorMessage varchar(max) out
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
begin tran
begin try
throw 51000, 'error occured', 1
commit tran
set @ErrorMessage = ''
end try
begin catch
set @ErrorMessage = ERROR_MESSAGE();
if @@TRANCOUNT = 1
rollback tran
if @@TRANCOUNT > 1
commit tran
end catch
END
create PROCEDURE [dbo].[Pouter]
-- Add the parameters for the stored procedure here
@ErrorMessage varchar(max) out
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
begin tran
begin try
EXECUTE [dbo].[Pinner]
@ErrorMessage OUTPUT
if @ErrorMessage <> '' begin
throw 51000, @ErrorMessage, 1
end
commit tran
set @ErrorMessage = ''
end try
begin catch
set @ErrorMessage = ERROR_MESSAGE();
if @@TRANCOUNT = 1
rollback tran
if @@TRANCOUNT > 1
commit tran
end catch
END
DECLARE @ErrorMessage varchar(max)
EXEC [dbo].[Pouter]
@ErrorMessage = @ErrorMessage OUTPUT
SELECT @ErrorMessage as N'@ErrorMessage'
https://www.codemag.com/article/0305111/handling-sql-server-errors-in-nested-procedures
这里有一种快速而简单的方法可以在存储过程中嵌套事务（使用Aaron答案中的代码），有时候会很有用。它使用默认参数来指示内部过程是否为嵌套调用，并向外部过程返回成功/失败结果。
CREATE PROCEDURE dbo.sp2
@trip BIT,
@nested BIT = 0
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON
IF @nested = 0 BEGIN TRAN
PRINT @@TRANCOUNT
IF @trip = 1
BEGIN
IF @nested = 0 ROLLBACK
RETURN 1
END
ELSE
BEGIN
IF @nested = 0 COMMIT
END
PRINT @@TRANCOUNT
RETURN 0
END
GO
外部过程检查成功/失败并在必要时回滚事务。
CREATE PROCEDURE dbo.sp1
@trip BIT
AS
BEGIN
DECLARE @result INT
SET NOCOUNT, XACT_ABORT ON
BEGIN TRAN
PRINT @@TRANCOUNT
BEGIN TRY
EXEC @result = dbo.sp2 @trip = @trip, @nested = 1
IF @result <> 0
BEGIN
ROLLBACK
RETURN 1
END
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
PRINT @@TRANCOUNT
COMMIT
PRINT @@TRANCOUNT
RETURN 0
END
GO
USE [DemoProject]
GO
/****** Object: StoredProcedure [dbo].[Customers_CRUD] Script Date: 11-Jan-17 2:57:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Customers_CRUD]
@Action VARCHAR(10)
,@BId INT = NULL
,@Username VARCHAR(50) = NULL
,@Provincename VARCHAR(50) = NULL
,@Cityname VARCHAR(50) = NULL
,@Number VARCHAR(50) = NULL
,@Name VARCHAR(50) = NULL
,@ContentType VARCHAR(50) = NULL
,@Data VARBINARY(MAX) = NULL
AS
BEGIN
SET NOCOUNT ON;
--SELECT
IF @Action = 'SELECT'
BEGIN
SELECT BId , Username,Provincename,Cityname,Number,Name,ContentType, Data
FROM tblbooking
END
--INSERT
IF @Action = 'INSERT'
BEGIN
INSERT INTO tblbooking(Username,Provincename,Cityname,Number,Name,ContentType, Data)
VALUES (@Username ,@Provincename ,@Cityname ,@Number ,@Name ,@ContentType ,@Data)
END
--UPDATE
IF @Action = 'UPDATE'
BEGIN
UPDATE tblbooking
SET Username = @Username,Provincename = @Provincename,Cityname = @Cityname,Number = @Number,Name = @Name,ContentType = @ContentType,Data = @Data
WHERE BId = @BId
END
--DELETE
IF @Action = 'DELETE'
BEGIN
DELETE FROM tblbooking
WHERE BId = @BId
END
END
GO