預存程序輸出參數、傳回值、返回表及C#調用，

預存程序輸出參數、傳回值、返回表及C#調用，

預存程序中可以定義輸出變數，傳回值，執行預存程序還能獲得結果集。每個預存程序的預設傳回值為0。下面緊接著上文 SQL Server中預存程序Stored Procedure建立及C#調用 基礎上寫的一個新的Stored Procedure預存程序則包含了輸出參數、傳回值以及select結果。

USE [db]GO/****** Object:  StoredProcedure [dbo].[insert_persions]    Script Date: 2/25/2015 11:14:11 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF OBJECT_ID ('get_persons','p') IS NOT NULL    DROP PROCEDURE get_persons;GO-- =============================================-- Author:      <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================CREATE PROCEDURE get_persons    -- Add the parameters for the stored procedure here    @firstname varchar(255),    @ret int = 0 OUTPUTASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;    -- Insert statements for procedure here    SELECT * FROM Persons    WHERE FirstName = @firstname;    SET @ret = @@ROWCOUNT    RETURN 10ENDGODECLARE @retVal int, @status intEXECUTE @status = get_persons "San",@ret = @retVal OUTPUTSELECT @retVal as N'Output val',@status as N'Ruturn val'

輸出參數
輸出參數的定義位置和預存程序的一般參數一樣，只不過會在後面增加一個OUTPUT關鍵字。在過程體中然後給參數使用SET指定輸出值。
執行包含輸出參數得到過程體的SQL語句中，也需要先定義變數，再將變數作為參數傳入執行語句中同時加上OUTPUT。如上例子所示：EXECUTE get_persons “San”,@ret = @retVal OUTPUT

傳回值
預存程序一般會有預設的傳回值為0，但是我們也可以在過程體要結束的位置或者某個分支塊結束語句處加上RETURN VALUE語句。
執行過程體的時候，如果需要同時擷取得到的傳回值，可以先定義變數，再使用“變數=”放在EXECUTE後面從而獲得執行結果值。例子就是：EXECUTE @status = get_persons “San”,@ret = @retVal OUTPUT

上述例子中的預存程序語句在SQL Server Management Studio執行完畢後，會每個select語句產生一個Table結果，如所示：

C#調用
上面例子中給出了SQL語句應對輸出參數、傳回值及SELECT表的情況。如果我們需要用C#來執行預存程序同時也希望得到所有的這些結果，可以按如下代碼來：

            String conStr = @"Data Source=HOST\SQLEXPRESS;Initial Catalog=DB;Integrated Security=SSPI";            SqlConnection con = new SqlConnection(conStr);            try            {                con.Open();                Console.WriteLine("Connect Sucess!");                SqlCommand com = new SqlCommand();                com.CommandType = System.Data.CommandType.StoredProcedure;                com.Connection = con;                com.CommandText = "get_persons";                SqlParameter pFirstName = new SqlParameter("@firstname", "San");                SqlParameter para = new SqlParameter("@ret", SqlDbType.Int);                para.Direction = ParameterDirection.Output;                 com.Parameters.Add(pFirstName);                com.Parameters.Add(para);                com.Parameters.Add("@status", SqlDbType.Int);                com.Parameters["@status"].Direction = ParameterDirection.ReturnValue;                SqlDataReader sdr = com.ExecuteReader();                while (sdr.Read())                {                    for (int i = 0; i < sdr.FieldCount; i++)                        Console.Write(sdr[i].ToString() + " ");                    Console.WriteLine();                }                sdr.Close();                Console.WriteLine(com.Parameters["@ret"].Value);                Console.WriteLine(com.Parameters["@status"].Value);            }            catch (Exception e)            {                Console.WriteLine(e.ToString());            }            finally            {                con.Close();            }

從中我們可以看出，對於輸出參數和傳回值，都是增加與預存程序一樣的參數名及類型，同時指定ParameterDirection，分別為OUTPUT和RETURNVALUE。而擷取執行結果則可以再命令執行完畢後擷取命令參數的值“com.Parameters[“@ret”].Value”。

對於無需擷取SELECT結果的情況，我們可以直接用命令ExecuteNonQuery即可。可是如果我們還想得到預存程序執行所得到的表結果呢？這時候一個辦法是利用“SqlDataReader sdr = com.ExecuteReader();”執行然後通過sdr.Read()來讀取SELECT結果。這裡需要注意的是，根據個人實踐，sdr讀完需要關係後才能夠擷取到返回參數及傳回值。