C# 執行存儲過程並取得返回值：SqlParameter Direction 完整範例

需求

呼叫 SQL Server 預存程序，取得 `RETURN` 值或 `OUTPUT` 參數。

預存程序範例

CREATE PROCEDURE usp_InsertUser
    @Name NVARCHAR(50),
    @Email NVARCHAR(100),
    @NewId INT OUTPUT
AS
BEGIN
    INSERT INTO Users(Name, Email) VALUES(@Name, @Email);
    SET @NewId = SCOPE_IDENTITY();
    RETURN 0; -- 成功返回 0
END

C# 完整呼叫代碼

using System;
using System.Data;
using System.Data.SqlClient;

public int InsertUser(string name, string email)
{
    using (var conn = new SqlConnection("Server=.;Database=TestDB;Integrated Security=true;"))
    using (var cmd = new SqlCommand("usp_InsertUser", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        // 輸入參數
        cmd.Parameters.AddWithValue("@Name", name);
        cmd.Parameters.AddWithValue("@Email", email);

        // 輸出參數：必須指定 Direction 和 Size (字串型別)
        var pNewId = new SqlParameter("@NewId", SqlDbType.Int)
        {
            Direction = ParameterDirection.Output
        };
        cmd.Parameters.Add(pNewId);

        // 返回值參數：參數名固定為 @RETURN_VALUE (或不指定名稱)
        var pReturn = new SqlParameter("@RETURN_VALUE", SqlDbType.Int)
        {
            Direction = ParameterDirection.ReturnValue
        };
        cmd.Parameters.Add(pReturn);

        conn.Open();
        cmd.ExecuteNonQuery();

        // 讀取輸出參數
        int newId = (int)pNewId.Value;
        int returnCode = (int)pReturn.Value;

        Console.WriteLine($"新用戶 ID: {newId}, 返回碼: {returnCode}");
        return newId;
    }
}

關鍵點總結

常見錯誤

1. 忘記 `Direction = Output`：參數值永遠為 null。
2. 字串輸出參數未設 `Size`：`new SqlParameter("@Msg", SqlDbType.NVarChar, 200) { Direction = ParameterDirection.Output }`
3. 使用 `ExecuteScalar` 取返回值：`ExecuteScalar` 回傳第一欄第一列，**不是** Return Value。必須用 `ExecuteNonQuery` 後讀參數。
4. 參數名稱大小寫：SQL Server 不區分大小寫，但建議與 SP 定義一致。

EF Core / Dapper 簡化寫法

Dapper

var p = new DynamicParameters();
p.Add("@Name", name);
p.Add("@Email", email);
p.Add("@NewId", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@RETURN_VALUE", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

conn.Execute("usp_InsertUser", p, commandType: CommandType.StoredProcedure);
int newId = p.Get("@NewId");

EF Core (FromSqlRaw + OUTPUT 子句替代)

// EF Core 不直接支援 SP Output 參數，建議改用 OUTPUT INSERT 子句
var id = await _context.Database.ExecuteSqlRawAsync(
    "INSERT INTO Users(Name, Email) OUTPUT INSERTED.Id VALUES(@p0, @p1)", name, email);

總結

**`SqlParameter.Direction = ParameterDirection.Output/ReturnValue`** 是核心。務必在 `ExecuteNonQuery()` **之後**讀取 `.Value`。現代專案優先用 Dapper 或 EF Core `OUTPUT` 子句，減少手寫 ADO.NET 樣板代碼。