Stored Procedure output parameters, return values, returned tables, and C # calls,
Output variables, return values, and result sets can be defined during stored procedures. The default return value for each stored procedure is 0. Next, a new Stored Procedure written based on the Stored Procedure created in SQL Server and C # called contains the output parameters, return values, and select results.
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 Parameters
The OUTPUT parameter definition location is the same as the general parameter of the stored procedure, but an OUTPUT keyword is added later. In the process body, specify the output value for the parameter using SET.
When executing an SQL statement that contains OUTPUT parameters to obtain the process body, you also need to define the variables first, and then pass the variables as parameters into the execution statement and add the OUTPUT. As shown in the preceding example: EXECUTE get_persons "San ",@ Ret = @ retVal OUTPUT
Return Value
The default return value of a stored procedure is 0. However, you can add the return value statement at the end of the procedure or at the end of a branch block.
When executing the process body, if you need to get the returned value at the same time, you can first define the variable, and then use "variable =" to put it After EXECUTE to get the execution result value. Example: EXECUTE@ Status =Get_persons "San", @ ret = @ retVal OUTPUT
In the preceding example, after the Stored Procedure statement is executed in SQL Server Management Studio, a Table result is generated for each select statement, as shown in:
C # Call
The preceding example shows how SQL statements handle output parameters, return values, and SELECT tables. If we need to use C # To execute the stored procedure and want to get all these results, we can use the following code:
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(); }
We can see that both OUTPUT parameters and return values Add the same parameter names and types as stored procedures, and specify ParameterDirection as OUTPUT and RETURNVALUE respectively. After obtaining the execution result, you can obtain the Value of the command parameter "com. Parameters [" @ ret "]. Value" after the command is executed ".
If you do not need to obtain the SELECT result, run ExecuteNonQuery. But what if we still want to get the table result from the execution of the stored procedure? At this time, one way is to use "SqlDataReader sdr = com. ExecuteReader ();" to execute and then Read the SELECT result through sdr. Read. It should be noted that, according to personal practice, the return parameters and return values can be obtained only after the sdr completes reading the relationship.