SQL Server 2014跨库动态SQL调用存储过程，事务是否生效？

好问题！在SQL Server 2014中，你描述的这种同一服务器跨库+动态SQL调用存储过程的场景下，事务是可以正常保证原子性（要么全部成功，要么全部回滚）的，但有几个关键细节需要你注意，我给你逐一说明：

核心原理：同一实例内的事务上下文共享 #

SQL Server的事务是基于数据库实例级别的，只要是同一个数据库连接中的操作，不管你访问的是实例下的哪个数据库（比如DB1或DB2），都会自动纳入当前的事务上下文。动态SQL默认会继承外层的事务环境，不会凭空创建独立事务——除非你在动态SQL代码里显式执行了BEGIN TRANSACTION这类操作。

正确的实现示例 #

下面是一个符合你需求的代码模板，你可以参考：

CREATE PROCEDURE Server1.DB1.dbo.OuterProcedure
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 步骤1：执行DB1内的业务操作
        INSERT INTO Server1.DB1.dbo.TargetTable (Column1) VALUES ('Sample Data');

        -- 步骤2：通过动态SQL调用DB2的存储过程
        DECLARE @DynamicSQL NVARCHAR(MAX) = N'
            EXEC Server1.DB2.dbo.InnerProcedure @InputParam = @Param;
        ';
        DECLARE @Param INT = 123;
        EXEC sp_executesql @DynamicSQL, N'@Param INT', @Param;

        -- 所有操作成功，提交事务
        COMMIT TRANSACTION;
        PRINT '事务已成功提交';
    END TRY
    BEGIN CATCH
        -- 捕获到错误，回滚整个事务
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- 输出错误信息并抛出，便于排查
        PRINT '事务已回滚，错误信息：' + ERROR_MESSAGE();
        THROW;
    END CATCH
END

在这个例子中，DB1的插入操作和动态SQL调用的DB2存储过程内的所有操作，都会被纳入同一个事务。只要其中任何一步出错，整个事务都会回滚，所有修改都会被撤销。

需要避开的坑 #

• 不要在动态SQL内显式管理事务：如果在动态SQL代码里执行BEGIN TRANSACTION、COMMIT或ROLLBACK，会干扰外层事务的计数逻辑。SQL Server的嵌套事务只是计数，内层COMMIT只会减少事务计数，而内层ROLLBACK会直接回滚整个事务，很容易导致逻辑混乱。
• 无需分布式事务（DTC）：你的场景是同一服务器下的跨库操作，不需要启用MS DTC。只有跨不同SQL Server实例（链接服务器）的操作才需要分布式事务支持。
• 确保数据库恢复模式兼容：虽然所有恢复模式都支持事务，但如果需要事务日志备份来恢复数据，建议使用完整恢复模式或大容量日志模式。

总结 #

只要你在外层存储过程中统一管理事务（用BEGIN TRANSACTION+TRY/CATCH+COMMIT/ROLLBACK的结构），并且不在动态SQL内额外操作事务，那么跨库的动态SQL调用完全可以保证事务的原子性，实现“要么全成，要么全滚”的需求。

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