SQL Server 存儲過程中切換數據庫上下文：EXEC('USE db') 技巧

限制背景

SQL Server **禁止**在存儲過程/觸發器中直接使用 USE dbname 切換數據庫上下文，編譯報錯。

變通方案：EXEC 動態執行

CREATE PROC test_cross_db
AS
BEGIN
    -- 在動態批次中切換上下文並執行操作
    EXEC('USE pubs; EXEC sp_adduser ''test_user'';');
END
GO

-- 執行
EXEC test_cross_db;

關鍵機制

• EXEC('...') 創建**獨立批次**，擁有自己的數據庫上下文
• 上下文切換**僅在該動態批次內有效**，外層過程上下文不變
• 所有需在目標庫執行的語句**必須打包在同一個 EXEC 字符串中**

實用模板：跨庫執行系統存儲過程

CREATE PROC ExecInDb
    @TargetDb SYSNAME,
    @Sql NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Wrapper NVARCHAR(MAX) = 
        N'USE ' + QUOTENAME(@TargetDb) + N'; ' + @Sql;
    EXEC(@Wrapper);
END

-- 調用：在 msdb 中創建 Job
EXEC ExecInDb 'msdb', N'EXEC sp_add_job @job_name = N''MyJob'';';

現代替代方案 (SQL Server 2012+)

• 包含數據庫：CREATE DATABASE ... CONTAINMENT = PARTIAL，用戶無需實例級登錄即可跨庫訪問
• 同義詞：CREATE SYNONYM dbo.t FOR OtherDb.dbo.t; 透明訪問
• 彈性查詢：Azure SQL / 連接服務器查詢遠程表

避坑提醒