萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 存儲過程調用完全指南:無參、輸出參數、動態調用、巢狀調用與異常處理

存儲過程是 SQL Server 封裝業務邏輯、提升性能、增強安全性的核心對象。本文系統梳理調用語法、參數傳遞、返回值處理、動態 SQL 調用、巢狀調用事務行為、錯誤處理模式,並附贈「查找字段屬於哪些表」的實用腳本。

📋 調用語法速查表

場景語法模板關鍵點
無參數EXEC dbo.ProcName;Schema 前綴最佳實踐
輸入參數EXEC dbo.ProcName @Param1 = 'Value', @Param2 = 100;命名參數順序無關、可選參數可省略
輸出參數DECLARE @Out INT; EXEC dbo.ProcName @OutParam = @Out OUTPUT; SELECT @Out;聲明變量 → OUTPUT 關鍵字 → 調用後讀取變量
返回值DECLARE @Ret INT; EXEC @Ret = dbo.ProcName ...; SELECT @Ret;INT 類型、默認 0 表示成功、非 0 表示錯誤碼
結果集INSERT INTO #Tmp EXEC dbo.ProcName ...;需預先建臨時表匹配列結構

🔧 完整示例:帶輸出參數的存儲過程

-- 1. 創建存儲過程
CREATE PROCEDURE dbo.CalcOrderStats
    @CustomerId INT,
    @OrderCount INT OUTPUT,
    @TotalAmount DECIMAL(18,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @OrderCount = COUNT(*), @TotalAmount = ISNULL(SUM(Total), 0)
    FROM Orders WHERE CustomerId = @CustomerId;
    RETURN 0; -- 成功
END;
GO

-- 2. 調用
DECLARE @Cnt INT, @Amt DECIMAL(18,2), @Ret INT;
EXEC @Ret = dbo.CalcOrderStats @CustomerId = 123, @OrderCount = @Cnt OUTPUT, @TotalAmount = @Amt OUTPUT;
SELECT @Ret AS ReturnCode, @Cnt AS OrderCount, @Amt AS TotalAmount;

🔄 動態 SQL 調用存儲過程

DECLARE @ProcName SYSNAME = N'dbo.CalcOrderStats';
DECLARE @Sql NVARCHAR(MAX) = N'EXEC ' + QUOTENAME(@ProcName) + N' @CustomerId = 123, @OrderCount = @Cnt OUT, @TotalAmount = @Amt OUT';
DECLARE @Cnt INT, @Amt DECIMAL(18,2);
EXEC sp_executesql @Sql, N'@Cnt INT OUTPUT, @Amt DECIMAL(18,2) OUTPUT', @Cnt OUTPUT, @Amt OUTPUT;
SELECT @Cnt, @Amt;

📦 巢狀調用與事務傳播

CREATE PROCEDURE dbo.OuterProc AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRAN;
        EXEC dbo.InnerProc1; -- 可能有自己的事務邏輯
        EXEC dbo.InnerProc2;
        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRAN;
        THROW; -- 重拋異常
    END CATCH
END;
  • 內層 SP 若有 BEGIN TRAN/COMMIT,外層 @@TRANCOUNT 會遞增
  • 錯誤時 XACT_STATE() = -1 表示不可提交事務,必須回滾
  • 建議:業務邊界由最外層控制事務,內層 SP 不主動管理事務,或使用 SAVE TRANSACTION 部分回滾

⚠️ 異常處理標準模式

CREATE PROCEDURE dbo.RobustProc
    @Input INT,
    @Result VARCHAR(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        -- 參數驗證
        IF @Input <= 0 THROW 50001, '輸入必須大於 0', 1;
        
        -- 核心邏輯
        SELECT @Result = 'Processed: ' + CAST(@Input AS VARCHAR);
        
        RETURN 0;
    END TRY
    BEGIN CATCH
        -- 記錄錯誤日誌(簡化版)
        DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE(),
                @ErrSeverity INT = ERROR_SEVERITY(),
                @ErrState INT = ERROR_STATE();
        RAISERROR(@ErrMsg, @ErrSeverity, @ErrState); -- 向客戶端拋錯
        RETURN -1; -- 非零返回碼
    END CATCH
END;

🔍 實用腳本:查找某列屬於哪些表

-- 標準信息視圖查詢(跨資料庫兼容性最好)
SELECT 
    TABLE_SCHEMA AS SchemaName,
    TABLE_NAME AS TableName,
    COLUMN_NAME AS ColumnName,
    DATA_TYPE AS DataType,
    CHARACTER_MAXIMUM_LENGTH AS MaxLength,
    IS_NULLABLE AS Nullable
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%TargetColumn%' -- 支援通配符
  AND TABLE_CATALOG = 'YourDB' -- 可選:限定資料庫
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

舊版 sysobjects/syscolumns 寫法僅供維護舊代碼參考,新代碼請用 INFORMATION_SCHEMA 或 sys.objects/sys.columns。

✅ 最佳實踐清單

  1. ✅ 總是使用 EXEC dbo.ProcName 兩部分命名
  2. ✅ 參數傳遞優先用 @Param = Value 命名方式
  3. ✅ 輸出參數**聲明、調用、讀取**三步缺一不可
  4. ✅ 返回值用 EXEC @Ret = Proc 捕獲,約定 0=成功
  5. ✅ 動態調用用 sp_executesql + 參數化,防注入
  6. ✅ 事務由最外層業務邊界控制,內層避免獨立提交
  7. ✅ TRY...CATCH + THROW 標準化異常處理
  8. ✅ 存儲過程首行 SET NOCOUNT ON 減少網絡往返
copyright © 萬盛學電腦網 all rights reserved