C# 快速取得預存程序回傳值與輸出參數技巧

情境

不想建立 `SqlCommand`/`SqlParameter` 完整物件圖，只想一行程式碼拿到預存程序的 `RETURN` 值或 `OUTPUT` 參數。

最簡寫法：動態 SQL 批次

using (var conn = new SqlConnection(connStr))
{
    conn.Open();
    // 宣告變數 -> 執行 SP -> 選取變數
    string sql = @"
        DECLARE @RetVal INT;
        EXEC @RetVal = dbo.MyProc @P1, @P2, @OutParam OUTPUT;
        SELECT @RetVal AS ReturnValue, @OutParam AS OutputParam;
    ";

    using (var cmd = new SqlCommand(sql, conn))
    {
        cmd.Parameters.AddWithValue("@P1", val1);
        cmd.Parameters.AddWithValue("@P2", val2);

        using (var reader = cmd.ExecuteReader())
        {
            if (reader.Read())
            {
                int returnValue = reader.GetInt32(0);   // RETURN 值
                int outParam  = reader.GetInt32(1);     // OUTPUT 參數
                // 後續處理...
            }
        }
    }
}

關鍵點

1. 使用 `DECLARE @Var TYPE; EXEC @Var = sp_name ...; SELECT @Var;` 彈性取得多種回傳。
2. `ExecuteReader` 配合單筆 `SELECT` 比 `ExecuteScalar` 更靈活 (可一次取多欄)。
3. 參數化查詢仍可防 SQL Injection，動態 SQL 只在批次文字中。

進階：包裝成泛型輔助方法

public static T ExecSpReturn<T>(string connStr, string spName, object[] paramValues, string outParamName = null)
{
    var sql = new StringBuilder($"DECLARE @RetVal INT; EXEC @RetVal = {spName} ");
    // 簡易參數佔位產生 (實務建議用 SqlCommandBuilder.DeriveParameters)
    for (int i = 0; i < paramValues.Length; i++)
        sql.Append(i == 0 ? "@p" + i : ", @p" + i);
    if (!string.IsNullOrEmpty(outParamName))
        sql.Append($", @{outParamName} OUTPUT");
    sql.Append($"; SELECT @RetVal AS RetVal{(!string.IsNullOrEmpty(outParamName) ? $", @{outParamName} AS OutParam" : "")};");

    using (var conn = new SqlConnection(connStr))
    using (var cmd = new SqlCommand(sql.ToString(), conn))
    {
        for (int i = 0; i < paramValues.Length; i++)
            cmd.Parameters.AddWithValue("@p" + i, paramValues[i] ?? DBNull.Value);

        conn.Open();
        using (var rd = cmd.ExecuteReader())
            if (rd.Read()) return rd.GetFieldValue<T>(0);
    }
    return default;
}

總結

透過 T-SQL 批次包裝 `DECLARE → EXEC → SELECT`，配合 `ExecuteReader`，即可輕鬆同時取得 `RETURN` 值與多個 `OUTPUT` 參數，避免繁瑣的 `SqlParameter` 方向設定，適合簡單呼叫場景。