[SQL Server] 如何接收 Store Procedure 的傳回值

2009-07-04

153649
0
C#
2009-07-05

當我們在撰寫處理資料庫的程式時，一般都會以 Store Procedure 來處理，一來執行速度較快，也比較安全及易於維護。
要將資料傳回一般有 3 種方式：
1. 使用 SELECT 以表格方式傳回。
2. 設定 Output Parameter 以參數方式傳回。
3. 使用 RETURN 傳回 1 個整數型別的資料。
以下列出這 3 種傳回方式，在 SQL 及 ADO.NET 中接收的方法。

當我們在撰寫處理資料庫的程式時，一般都會以 Store Procedure 來處理，一來執行速度較快，也比較安全及易於維護。

要將資料傳回一般有 3 種方式：

使用 SELECT 以表格方式傳回。
設定 Output Parameter 以參數方式傳回。
使用 RETURN 傳回 1 個整數型別的資料。

以下列出這 3 種傳回方式，在 SQL 及 ADO.NET 中接收的方法。

一、使用 SELECT 以表格方式傳回

我們先來看第 1 種，這也是我最常用的方式。

Store Procedure：


IF OBJECT_ID('mysp_QueryData') IS NOT NULL
    DROP PROCEDURE mysp_QueryData
GO
CREATE PROCEDURE mysp_QueryData (
@id int
)
AS
BEGIN
    SELECT LastName, FirstName
      FROM Customer
     WHERE CustomerID = @id;
END

在另一支預儲程序中要接收此預儲程序傳回的資料，方法如下：


IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp
GO
CREATE TABLE #tmp (lname varchar(30), fname varchar(10));
INSERT INTO #tmp EXEC mysp_QueryData '1';
SELECT * FROM #tmp;

1～2 行：判斷若暫存資料表已存在則進行刪除。

4～6 行：先建立暫存資料表，再使用 INSERT INTO 方式寫入後即可對暫存資料表操作取值。


SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(string.Empty, conn);
conn.Open();

cmd.Parameters.Clear();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "mysp_QueryData";
cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1;

SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

if (reader.HasRows)
{
    reader.Read();
    Console.WriteLine("Last Name：" + reader["LastName"]);
    Console.WriteLine("First Name：" + reader["FirstName"]);
}

reader.Close();
reader.Dispose();

conn.Close();
conn.Dispose();

此例是以 ExecuteReader() 來進行讀取，然後配合 SqlDataReader 物件將欄位值讀出。

因傳回的內容只有 1 個資料集，所以第 10 行在讀取時可以使用 CommandBehavior.SingleRow 來接收傳回的資料集，可結省資源及增進效率，但要注意，若是傳回的內容有 2 個資料集以上，那麼使用 CommandBehavior.SingleRow 就無法讀到第 2 個以後的資料集內容了。

假設傳回內容只有 1 個傳回值，或是只要讀取傳回的第 1 筆資料的第 1 個欄位值，可以使用 ExecuteScalar() 將 LastName 讀出，如此上面 10～20 行的程式可改為如下：


string strName = Convert.ToString(cmd.ExecuteScalar());
Console.WriteLine("Last Name：" + strName);

二、設定 Output Parameter 以參數方式傳回

Store Procedure：


CREATE PROCEDURE mysp_QueryData (
@id int,
@LastName Varchar(30) output
)
AS
BEGIN
    SELECT @LastName = LastName
      FROM Customer
     WHERE CustomerID = @id;
END

關鍵在第 3 行，在參數後方加上 output 設定為輸出參數。

在另一支預儲程序中要接收此預儲程序傳回的輸出參數方法如下：


DECLARE @lname varchar(30);
EXEC mysp_QueryData '1',@lname output;
PRINT @lname;

第 2 行：在執行預儲程序後方指定參數時，要接收輸出的參數設定 output 即可。


SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(string.Empty, conn);
conn.Open();

cmd.Parameters.Clear();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "mysp_QueryData";
cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1;

SqlParameter lastName = cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 30);
lastName.Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();

Console.WriteLine("Last Name：" + lastName.Value);

conn.Close();
conn.Dispose();

第 10 行：建立要接收輸出的參數名稱資料，參數名稱 @LastName 須與預儲程序中的參數名稱一樣。

第 11 行：設定為輸出參數。

第 15 行：用 .value 將傳回資料取出。

三、使用 RETURN 傳回單一數值

Store Procedure：


CREATE PROCEDURE mysp_InsData (
@LastName varchar(30),
@FirstName varchar(10)
)
AS
BEGIN
    INSERT INTO Customer (LastName, FirstName) VALUES (@LastName, @FirstName);
    RETURN SCOPE_IDENTITY();
END

在新增完資料後，使用 RETURN 返回新增資料的識別值。

在另一支預儲程序中接收此預儲程序傳回的值：


DECLARE @intRow int;
EXEC @intRow = mysp_InsData 'Walter', 'Liao';
SELECT @intRow;


SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(string.Empty, conn);
conn.Open();

cmd.Parameters.Clear();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "mysp_InsData";
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 30).Value = "Walter";
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = "Liao";

SqlParameter retID = cmd.Parameters.Add("@ReturnValue", SqlDbType.Int);
retID.Direction = ParameterDirection.ReturnValue;

try
{
    cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    //當新增資料時若發生錯誤，可撰寫程式將錯誤訊息記錄下來
    //InsErrorLog("mysp_InsData", ex.Message); 
}

Console.WriteLine("Return Value：" + retID.Value);

conn.Close();
conn.Dispose();

第 11 行：建立要接收 RETURN 的參數，參數名稱 @ReturnValue 可自定。

第 12 行：將參數設定為 Return Value。

第 24 行：用 .value 將傳回資料取出。

參考資料：

回首頁