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

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

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

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

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

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

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

Store Procedure：

01	IF OBJECT_ID('mysp_QueryData') IS NOT NULL

02	DROP PROCEDURE mysp_QueryData

03

GO

04	CREATE PROCEDURE mysp_QueryData (

05

@id int

06

)

07

AS

08

BEGIN

09	SELECT LastName, FirstName

10	FROM Customer

11	WHERE CustomerID = @id;

12

END

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

1	IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

2	DROP TABLE #tmp

3

GO

4	CREATE TABLE #tmp (lname varchar(30), fname varchar(10));

5	INSERT INTO #tmp EXEC mysp_QueryData '1';

6	SELECT * FROM #tmp;

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

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

C#

01	SqlConnection conn = new SqlConnection(strConn);

02	SqlCommand cmd = new SqlCommand(string.Empty, conn);

03	conn.Open();

04

05	cmd.Parameters.Clear();

06	cmd.CommandType = CommandType.StoredProcedure;

07	cmd.CommandText = "mysp_QueryData";

08	cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1;

09

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

11

12	if (reader.HasRows)

13

{

14	reader.Read();

15	Console.WriteLine("Last Name：" + reader["LastName"]);

16	Console.WriteLine("First Name：" + reader["FirstName"]);

17

}

18

19	reader.Close();

20	reader.Dispose();

21

22	conn.Close();

23	conn.Dispose();

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

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

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

1	string strName = Convert.ToString(cmd.ExecuteScalar());

2	Console.WriteLine("Last Name：" + strName);

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

Store Procedure：

01	CREATE PROCEDURE mysp_QueryData (

02

@id int,

03	@LastName Varchar(30) output

04

)

05

AS

06

BEGIN

07	SELECT @LastName = LastName

08	FROM Customer

09	WHERE CustomerID = @id;

10

END

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

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

1	DECLARE @lname varchar(30);

2	EXEC mysp_QueryData '1',@lname output;

3	PRINT @lname;

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

C#

01	SqlConnection conn = new SqlConnection(strConn);

02	SqlCommand cmd = new SqlCommand(string.Empty, conn);

03	conn.Open();

04

05	cmd.Parameters.Clear();

06	cmd.CommandType = CommandType.StoredProcedure;

07	cmd.CommandText = "mysp_QueryData";

08	cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1;

09

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

11	lastName.Direction = ParameterDirection.Output;

12

13	cmd.ExecuteNonQuery();

14

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

16

17	conn.Close();

18	conn.Dispose();

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

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

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

三、使用 RETURN 傳回單一數值

Store Procedure：

1	CREATE PROCEDURE mysp_InsData (

2	@LastName varchar(30),

3	@FirstName varchar(10)

4

)

5

AS

6

BEGIN

7	INSERT INTO Customer (LastName, FirstName) VALUES (@LastName, @FirstName);

8	RETURN SCOPE_IDENTITY();

9

END

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

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

1	DECLARE @intRow int;

2	EXEC @intRow = mysp_InsData 'Walter', 'Liao';

3	SELECT @intRow;

C#

01	SqlConnection conn = new SqlConnection(strConn);

02	SqlCommand cmd = new SqlCommand(string.Empty, conn);

03	conn.Open();

04

05	cmd.Parameters.Clear();

06	cmd.CommandType = CommandType.StoredProcedure;

07	cmd.CommandText = "mysp_InsData";

08	cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 30).Value = "Walter";

09	cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = "Liao";

10

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

12	retID.Direction = ParameterDirection.ReturnValue;

13

14

try

15

{

16	cmd.ExecuteNonQuery();

17

}

18	catch (Exception ex)

19

{

20	//當新增資料時若發生錯誤，可撰寫程式將錯誤訊息記錄下來

21	//InsErrorLog("mysp_InsData", ex.Message);

22

}

23

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

25

26	conn.Close();

27	conn.Dispose();

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

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

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