預存程序 Return

本節示範 ADO.NET 呼叫預存程序：透過 ExecuteNonQuery 取得受影響資料列數，同時接收預存程序的 Return 傳回值

一、資料庫預存程序定義

create procedure mynewproc(@id int)
as
begin
    declare @cout int
    -- 更新敘述
    update Article set title = '111' where id = @id
    -- 統計 id 大於傳入參數的資料筆數
    select @cout = count(1) from Article where id > @id
    -- return 傳回數值（SQL Server 預存程序 return 僅能傳回 int）
    return @cout
endCode language: PHP (php)

重點：return @cout 是預存程序的傳回值，和輸出參數 output 並不相同。

二、在 C# 當中呼叫

string connectionString = "Data Source=.;Initial Catalog=db;Integrated Security=SSPI;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand("mynewproc", connection);
    command.CommandType = CommandType.StoredProcedure;

    // 【重點】註冊 Return 傳回值參數，名稱固定為 ReturnValue
    SqlParameter para = new SqlParameter(
        "ReturnValue",
        SqlDbType.Int,
        4,
        ParameterDirection.ReturnValue,
        false,0,0,string.Empty,DataRowVersion.Default,null);
    command.Parameters.Add(para);

    // 預存程序輸入參數 @id
    SqlParameter param = new SqlParameter("@id", SqlDbType.Int, 8);
    param.Value = 4;
    param.Direction = ParameterDirection.Input;
    command.Parameters.Add(param);

    // ExecuteNonQuery：執行新增修改刪除，回傳【update 敘述受影響的資料列數】
    int rowsAffected = command.ExecuteNonQuery();
    // 讀取預存程序 return 所帶回的數值
    int result = (int)command.Parameters["ReturnValue"].Value;
}Code language: PHP (php)

建議寫法

string connectionString = "Data Source=.;Initial Catalog=db;Integrated Security=SSPI;";
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("mynewproc", conn))
{
    conn.Open();
    cmd.CommandType = CommandType.StoredProcedure;

    // 註冊傳回值參數（名稱固定：ReturnValue，方向必須設定為 ReturnValue）
    cmd.Parameters.Add("ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
    // 輸入參數
    cmd.Parameters.Add("@id", SqlDbType.Int).Value = 4;

    // rowsAffected = update 敘述實際修改的資料筆數
    int rowsAffected = cmd.ExecuteNonQuery();
    // 取得預存程序 return @cout 的數值
    int procReturnVal = (int)cmd.Parameters["ReturnValue"].Value;
}Code language: JavaScript (javascript)

注意事項

1. int rowsAffected = command.ExecuteNonQuery();

• 意義：DML 敘述（update/delete/insert）所影響的資料列數
• 本範例：update Article set title='111' where id=@id 比對並更新的資料筆數
• 不等於預存程序 return 的傳回值，兩者完全互相獨立

2. ReturnValue 傳回值參數

1. 參數名稱強制使用固定字串 ReturnValue，不可自訂名稱
2. Direction = ParameterDirection.ReturnValue
3. SQL Server 預存程序的 return 數值 僅能傳遞 int 型別
4. 必須執行完 ExecuteNonQuery() 之後，才可以讀取此參數內容

• return 傳回值：僅能有一個，限 int 型別
• output 輸出參數：可設定多個，支援各式資料型別

兩者差異

-- 預存程序內部
update Article set title='111' where id=4; -- 假設更新 1 筆資料
return 10; -- return 傳回值Code language: PHP (php)

執行結果：

• rowsAffected = 1（update 影響的資料列數）
• procReturnVal = 10（return 攜帶的數值）

總整理

方法	回傳內容	適用場景
ExecuteReader	SqlDataReader（流式多筆資料）	查詢多筆紀錄
SqlDataAdapter.Fill	DataSet/DataTable（離線資料表）	WinForm 控制項繫結表格
ExecuteScalar	object（第一列第一欄）	只需要單一彙總結果
ExecuteNonQuery	int(受影響列數)+ReturnValue傳回值	新增/更新/刪除，需要預存程序回傳狀態碼

預存程序 Return