.NET 呼叫預存程序並取得 RETURN 值的正確姿勢

預存程序的 RETURN 只能回傳 int，若需回傳字串或多值請改用 OUTPUT 參數。本文示範使用 SqlParameter 設定 Direction = ParameterDirection.ReturnValue 正確讀取返回值。

1. 預存程序範例

CREATE PROCEDURE GetOrderLine
    @OrderId VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM OrderLine WHERE OrderId = @OrderId;
    RETURN 123;  -- 僅能回傳 int
END

2. ADO.NET 正確呼叫代碼

using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("GetOrderLine", conn) { CommandType = CommandType.StoredProcedure })
{
    // 輸入參數
    cmd.Parameters.Add("@OrderId", SqlDbType.VarChar, 50).Value = "43c7cf15-6b2f-4d18-92b2-dbe827f30dfc";

    // 返回值參數：務必設定 Direction = ReturnValue
    var returnParam = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
    returnParam.Direction = ParameterDirection.ReturnValue;

    conn.Open();

    // 取得結果集
    var dt = new DataTable();
    using (var adapter = new SqlDataAdapter(cmd))
    {
        adapter.Fill(dt);
    }

    // 讀取 RETURN 值（必須在讀完結果集/關閉 DataReader 後）
    int returnCode = (int)returnParam.Value;
    Console.WriteLine($"預存程序返回碼: {returnCode}"); // 123

    // 處理結果集
    foreach (DataRow row in dt.Rows)
    {
        Console.WriteLine($"{row["ProductId"]}:{row["ProductPrice"]}:{row["ProductCount"]}");
    }
}

關鍵注意事項

進階：同時使用 OUTPUT 參數回傳多值

CREATE PROCEDURE GetOrderSummary
    @OrderId VARCHAR(50),
    @TotalAmount DECIMAL(18,2) OUTPUT,
    @ItemCount INT OUTPUT
AS
BEGIN
    SELECT @TotalAmount = SUM(Price * Qty), @ItemCount = COUNT(*)
    FROM OrderLine WHERE OrderId = @OrderId;
    RETURN 0; -- 成功碼
END

var totalParam = cmd.Parameters.Add("@TotalAmount", SqlDbType.Decimal);
totalParam.Direction = ParameterDirection.Output;
totalParam.Precision = 18; totalParam.Scale = 2;

var countParam = cmd.Parameters.Add("@ItemCount", SqlDbType.Int);
countParam.Direction = ParameterDirection.Output;

// ... 執行後 ...
decimal total = (decimal)totalParam.Value;
int count = (int)countParam.Value;

常見錯誤與修正

• 忘設 Direction：預設為 Input，無法接收返回值。
• 在 DataReader 開啟時讀返回值：會得到 null 或 0，必須先關閉 Reader。
• 用 AddWithValue 設定 Output/ReturnValue：無法指定 Direction，需用 Add 後單獨設定。