让我们说这是情况：
[Stored Proc 1]
BEGIN
BEGIN TRANSACTION
...
exec sp 2
COMMIT
END
现在,如果SP 2 – 由于某种原因回滚,SP 1 – 提交或回滚还是抛出异常？
谢谢.
解决方法
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;
结果：
120 <– notice that a rollback here rolled back both Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. PrevIoUs count = 1,current count = 0. 00
