Output Parameters, return values, and result sets of Stored Procedures

Each stored procedure has a default return value. The default value is 0. Next we will look at how to view the output parameters, return values, and result set in management studio, and then how to obtain the output parameters, return values, and result set in the ASP. NET call stored procedure.

First, view the output parameters, return values, and result set in SQL server management studio. This example uses the Northwind database as an example.
Copy codeThe Code is as follows:
Create proc Employee
@ Rowcount int = 0 output
As
Begin
SELECT * FROM [Northwind]. [dbo]. [Employees]
Set @ Rowcount = @ ROWCOUNT
End

The code for running the above stored procedure is as follows:
Run code
Copy codeThe Code is as follows:
USE [Northwind]
GO
DECLARE @ return_value int,
@ MyOutput int
EXEC @ return_value = [dbo]. [Employee]
@ Rowcount = @ MyOutput OUTPUT
SELECT @ MyOutput as n' value of the output parameter'
SELECT 'Return returned value' = @ return_value
GO

The output result is as follows:

It can be seen that EXEC @ return_value = [dbo]. [Employee] @ Rowcount = @ MyOutput the return_value variable in the OUTPUT to obtain the returned value, and the OUTPUT variable value is obtained using the MyOutput variable. The MyOutput variable here is equivalent to the reference passed!
Next, we will discuss how ASP. NET obtains output parameters, return values, and result sets in the stored procedure.

Copy codeThe Code is as follows:
Protected void Page_Load (object sender, EventArgs e)
{
SqlConnection con = new SqlConnection ("server =.; database = Northwind; uid = sa; pwd = 1 ;");
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = con;
Cmd. CommandText = "Employee ";
Cmd. CommandType = CommandType. StoredProcedure;
SqlParameter para = new SqlParameter ("@ Rowcount", SqlDbType. Int, 4 );
Para. Direction = ParameterDirection. Output;
Cmd. Parameters. Add (para );
Cmd. Parameters. Add ("@ return_value", SqlDbType. Int, 4 );
Cmd. Parameters ["@ return_value"]. Direction = ParameterDirection. ReturnValue;
Con. Open ();
Cmd. ExecuteNonQuery ();
Response. Write (cmd. Parameters ["@ Rowcount"]. Value. ToString () + "<br/> ");
Response. Write (cmd. Parameters ["@ return_value"]. Value. ToString ());
}

Rowcount output variable and return_value return value variable are defined here. The value of the output Roucount variable is 9, and the value of return_value is 0.