# ADO.NET 如何取得 Stored Procedure 的回傳值

若使用 ADO.NET 連接資料庫並呼叫預儲程序(Stored Procedure)的話，基本上有三種方式可以取得執行後的結果，分別如下：

1.  在預儲程序中最後一行直接用 SELECT 語法回傳表格資料
2.  在預儲程序中使用輸出參數(Output Parameter)的方式回傳資料
3.  在預儲程序中使用 RETURN 語法回傳一個整數型別(Int32)的狀態碼

若是呼叫的預儲程序不需要回傳值，基本上可以用以下程式執行，也是蠻標準的寫法：

```
SqlConnection db = new SqlConnection(connStr);

SqlCommand cmd = new SqlCommand("sp_MyStoredProcedure", db);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@DataID",SqlDbType.VarChar, 15);                
cmd.Parameters["@DataID"].Value = "XXXXX";

try
{
    db.Open();
    cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    throw ex.GetBaseException();
}
finally
{
    db.Close();
}
```

如果在預儲程序回傳的是一個表格資料型態，就可以將 [ExecuteNonQuery()](http://msdn.microsoft.com/zh-tw/library/system.data.sqlclient.sqlcommand.executenonquery\(VS.80\).aspx) 改成 [ExecuteReader()](http://msdn.microsoft.com/zh-tw/library/9kcbe65k\(VS.80\).aspx) 方法，取回的 [SqlDataReader](http://msdn.microsoft.com/zh-tw/library/system.data.sqlclient.sqldatareader\(VS.80\).aspx) 物件就可以直接針對回傳的資料表進行存取。

若回傳的是一個單一值(Scalar Value)，也可以用 [ExecuteScalar()](http://msdn.microsoft.com/zh-tw/library/system.data.sqlclient.sqlcommand.executescalar\(VS.80\).aspx) 方法直接取得回傳值。有個比較常見的使用情況是當在預儲程序中新增資料時，要直接回傳新增資料的 ID 值(Last Inserted ID)，你可以直接用 SELECT 語法直接回傳 [SCOPE\_IDENTITY()](http://technet.microsoft.com/zh-tw/library/ms190315.aspx) 的資料，如下預儲程序範例：

```
CREATE PROCEDURE sp_AddNewItem
(
    @Name    nvarchar(50)
)
AS

INSERT INTO [dbo].[Category] (Name) VALUES (@Name)

SELECT SCOPE_IDENTITY()
```

程式碼範例如下：

```
SqlConnection db = new SqlConnection(connStr);

SqlCommand cmd = new SqlCommand("sp_AddNewItem", db);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@Name",SqlDbType.NVarChar, 50);
cmd.Parameters["@Name"].Value = "Category 1";

try
{
    db.Open();
    int LastInsertedID = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
    throw ex.GetBaseException();
}
finally
{
    db.Close();
}
```

如果用輸出參數(Output Parameter)的方式回傳資料，那只要多定義一個參數，並指定 Direction 屬性為 ParameterDirection.Output 即可在執行 [ExecuteNonQuery()](http://msdn.microsoft.com/zh-tw/library/system.data.sqlclient.sqlcommand.executenonquery\(VS.80\).aspx) 方法後得到回傳的資料，如下範例：

```
SqlConnection db = new SqlConnection(connStr);

SqlCommand cmd = new SqlCommand("sp_MyStoredProcedure", db);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@DataID",SqlDbType.VarChar, 15);                
cmd.Parameters["@DataID"].Value = "XXXXX";

SqlParameter retValParam = cmd.Parameters.Add("@OutputData", SqlDbType.VarChar, 250);
retValParam.Direction = ParameterDirection.Output;

try
{
    db.Open();
    cmd.ExecuteNonQuery();
    Console.Write("取得的輸出資料: " + retValParam.Value);
}
catch (Exception ex)
{
    throw ex.GetBaseException();
}
finally
{
    db.Close();
}
```

最後一種，若你的預儲程序僅需要回傳整數型別的資料，可以在預儲程序中直接用 [RETURN](http://msdn.microsoft.com/zh-tw/library/ms174998.aspx) 語法回傳，而要在 C# 中取得回傳值，用法類似輸出參數(Output Parameter)的方式，只要把要取得的參數 Direction 屬性為 ParameterDirection.ReturnValue 即可，如下範例：

```
SqlConnection db = new SqlConnection(connStr);

SqlCommand cmd = new SqlCommand("sp_MyStoredProcedure", db);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@DataID",SqlDbType.VarChar, 15);                
cmd.Parameters["@DataID"].Value = "XXXXX";

SqlParameter retValParam = cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.VarChar, 250);
retValParam.Direction = ParameterDirection.ReturnValue;

try
{
    db.Open();
    cmd.ExecuteNonQuery();
    Console.Write("取得的輸出資料: " + retValParam.Value);
}
catch (Exception ex)
{
    throw ex.GetBaseException();
}
finally
{
    db.Close();
}
```

備註：所有**系統預存程序**若執行成功都會回傳 0，若得到非 0 的值表示失敗。（除非文件另有說明）

**相關連結**

-   [連接及擷取 ADO.NET 中的資料](http://msdn.microsoft.com/zh-tw/library/ms254937\(VS.80\).aspx)
-   [SqlDataReader 類別](http://msdn.microsoft.com/zh-tw/library/system.data.sqlclient.sqldatareader\(VS.80\).aspx)
-   [SqlCommand.ExecuteReader 方法](http://msdn.microsoft.com/zh-tw/library/9kcbe65k\(VS.80\).aspx)
-   [SqlCommand.ExecuteNonQuery 方法](http://msdn.microsoft.com/zh-tw/library/system.data.sqlclient.sqlcommand.executenonquery%28VS.80%29.aspx)
-   [SqlCommand.ExecuteScalar 方法](http://msdn.microsoft.com/zh-tw/library/system.data.sqlclient.sqlcommand.executescalar\(VS.80\).aspx)
-   [SCOPE\_IDENTITY (Transact-SQL)](http://technet.microsoft.com/zh-tw/library/ms190315.aspx)
-   [RETURN (Transact-SQL)](http://msdn.microsoft.com/zh-tw/library/ms174998.aspx)