SQL Server 2014嵌套存储过程添加事务与回滚能否正常生效？

首先先看你提供的主存储过程代码：

ALTER PROCEDURE TemplateBatchDynamicFromRawDataSetTemplateRelnInsert 
    @FileData VARBINARY(MAX) = NULL, 
    @ActualFileName NVARCHAR(MAX) = NULL, 
    @UserID INT = NULL, 
    @ClientID INT = NULL, 
    @SelectedSheet NVARCHAR(MAX) = NULL, 
    @ACAFileNotes NVARCHAR(MAX) = NULL, 
    @ClientTemplateNotes NVARCHAR(MAX) = NULL, 
    @SourceReportFile NVARCHAR(500) = NULL, 
    @TemplateIDs INT = 0 
AS 
BEGIN 
    BEGIN TRANSACTION tr 
    BEGIN 
        DECLARE @TemplateIDTab as table (TemplateID INT) 
        INSERT @TemplateIDTab(TemplateID) EXEC [TemplateFileInsert] @FileData, @ActualFileName, @UserID, @ClientID, @SelectedSheet, @TemplateIDs 
        DECLARE @TemplateID INT = (SELECT TOP 1 TemplateID FROM @TemplateIDTab) 
        EXEC [TemplateDetailsInsert] @TemplateID, '', @SourceReportFile, @ClientTemplateNotes, @ACAFileNotes, 3 
        SELECT @TemplateID TemplateID 
    END 
    COMMIT TRANSACTION tr 
    IF(@@ERROR > 0) ROLLBACK TRANSACTION tr 
END 
GO

先指出当前代码的核心问题 #

你的错误处理逻辑顺序完全错了：现在是先执行COMMIT TRANSACTION，再检查@@ERROR。一旦提交完成，后续的ROLLBACK根本不会生效——因为事务已经不存在了。而且@@ERROR只能捕获上一条语句的错误，要是TemplateFileInsert或者TemplateDetailsInsert执行出错，@@ERROR的数值不会被正确传递到最后检查的位置。

SQL Server 2014中嵌套事务的实际行为 #

SQL Server并没有真正意义上的“嵌套事务”，它的事务是基于**事务计数（@@TRANCOUNT）**来管理的：

• 每次执行BEGIN TRANSACTION，@@TRANCOUNT加1；
• 每次执行COMMIT TRANSACTION，@@TRANCOUNT减1；只有当@@TRANCOUNT减到0时，才会真正提交整个事务；
• 如果任何一个嵌套的存储过程执行了ROLLBACK TRANSACTION，不管当前@@TRANCOUNT是多少，都会直接回滚整个事务，并把@@TRANCOUNT重置为0。这时候主存储过程再执行COMMIT就会报错，因为已经没有活跃的事务了。

所以如果你的TemplateFileInsert或TemplateDetailsInsert内部包含自己的事务逻辑：

• 如果内部存储过程执行了COMMIT，只是减少事务计数，不会真正提交，直到主存储过程的COMMIT把计数降到0；
• 如果内部存储过程执行了ROLLBACK，整个主事务都会被回滚，主存储过程后续的操作都会失效。

修正后的主存储过程代码（用TRY/CATCH实现可靠的事务处理） #

在SQL Server 2014中，推荐用TRY/CATCH块来处理事务，它能更可靠地捕获所有执行错误，包括内部存储过程抛出的错误：

ALTER PROCEDURE TemplateBatchDynamicFromRawDataSetTemplateRelnInsert 
    @FileData VARBINARY(MAX) = NULL, 
    @ActualFileName NVARCHAR(MAX) = NULL, 
    @UserID INT = NULL, 
    @ClientID INT = NULL, 
    @SelectedSheet NVARCHAR(MAX) = NULL, 
    @ACAFileNotes NVARCHAR(MAX) = NULL, 
    @ClientTemplateNotes NVARCHAR(MAX) = NULL, 
    @SourceReportFile NVARCHAR(500) = NULL, 
    @TemplateIDs INT = 0 
AS 
BEGIN 
    SET NOCOUNT ON;
    DECLARE @TemplateID INT;
    DECLARE @TemplateIDTab as table (TemplateID INT);

    BEGIN TRY
        BEGIN TRANSACTION tr;

        -- 调用内部存储过程并获取TemplateID
        INSERT @TemplateIDTab(TemplateID) 
        EXEC [TemplateFileInsert] @FileData, @ActualFileName, @UserID, @ClientID, @SelectedSheet, @TemplateIDs;

        SELECT @TemplateID = TOP 1 TemplateID FROM @TemplateIDTab;

        -- 调用第二个内部存储过程
        EXEC [TemplateDetailsInsert] @TemplateID, '', @SourceReportFile, @ClientTemplateNotes, @ACAFileNotes, 3;

        -- 提交事务（只有当所有操作都成功时才会执行）
        COMMIT TRANSACTION tr;

        -- 返回结果
        SELECT @TemplateID AS TemplateID;
    END TRY
    BEGIN CATCH
        -- 如果有错误，检查是否有活跃事务并回滚
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION tr;

        -- 抛出错误信息（可以根据需求调整，比如返回错误码/消息）
        THROW;
    END CATCH
END 
GO

额外注意事项 #

• 内部存储过程的ROLLBACK影响：如果TemplateFileInsert或TemplateDetailsInsert内部执行了ROLLBACK，会直接触发主存储过程的CATCH块，整个事务被回滚。如果你希望内部错误只回滚自身操作而不影响主事务，需要在内部存储过程中使用保存点（SAVE TRANSACTION），而不是直接ROLLBACK——但保存点的使用要非常谨慎，因为它不会重置@@TRANCOUNT，只是回滚到指定的保存点位置。
• SET XACT_ABORT ON：可以考虑在存储过程开头加上SET XACT_ABORT ON;，它会在执行错误时自动终止批处理并回滚事务，避免一些潜在的未提交事务问题。

内容的提问来源于stack exchange，提问作者Jeeno