Stored procedure output parameters, return values, return tables, and C # calls

The stored procedure can define output variables, return values, and execute stored procedures to obtain a result set. The default return value for each stored procedure is 0. A new stored procedure stored procedure, based on the stored procedure stored procedure creation and C # invocation in SQL Server above, contains 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 NULLDROP  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  Span class= "Hljs-keyword" >output  as  begin --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 toENDGODECLARE @retVal int, @st atus intEXECUTE @status = get_persons "San", @ret = @retVal OUTPUTSELECT @retVal  as N' Output val ', @status  as N' Ruturn val ' 

Output Parameters
The output parameter is defined in the same way as the general parameters of the stored procedure, except that it adds an output keyword later. In the procedure body, then specify the output value with set for the parameter.
Executing an SQL statement that contains an output parameter to get the body of the procedure, you also need to define the variable, and then add the variable as an argument to the execution statement with output. As shown in the example above: EXECUTE get_persons "San",@ret = @retVal OUTPUT

return value
The stored procedure typically has a default return value of 0, but we can also add a return value statement at the end of the procedure body or at a branch block closing sentence.
When executing the process body, if you need to get the resulting return value at the same time, you can define the variable and then use "variable =" after execute to obtain the execution result value. Example: EXECUTE @status = get_persons "San", @ret = @retVal OUTPUT

The stored procedure statements in the example above will generate a table result for each SELECT statement after SQL Server Management Studio finishes executing, as shown in:

C # calls
In the example above, the SQL statement should address the output parameters, return values, and select tables. If we need to execute the stored procedure in C # and want to get all of these results, we can do this in 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!");SqlCommandcom= 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();}

As we can see, for the output parameter and the return value, we increment the same parameter name and type as the stored procedure, and specify ParameterDirection, respectively, output and returnvalue. When you get the execution result, you can then get the value of the command parameter "com" after the command has finished executing. parameters["@ret"]. Value ".

For situations where you do not need to obtain a select result, we can use the command ExecuteNonQuery directly. But what if we still want to get the results of the table that the stored procedure executes? One way to do this is to use "SqlDataReader SDR = com." ExecuteReader (); " Execute and then pass the SDR. Read () reads the select result. It is important to note that, according to the individual practice, the SDR will be able to retrieve the return parameters and return values after reading the required relationship.

Stored procedure output parameters, return values, return tables, and C # calls