In this article I will explain with an example, how to return Output parameter from Stored Procedure in ASP.Net using C# and VB.Net.
 
 

Database

I have made use of the following table Fruits with the schema as follows.
How to return Output parameter from Stored Procedure in ASP.Net using C# and VB.Net
 
I have already inserted few records in the table.
How to return Output parameter from Stored Procedure in ASP.Net using C# and VB.Net
 
 

Stored Procedure

The SQL Server Stored Procedure accepts the following parameters:-
1. FruitId – This is an INPUT Parameter used to pass the FruitId.
2. FruitName – This is an OUTPUT Parameter used to fetch the Name of the Fruit based on its FruitId.
Note: Output Parameter is identified by the keyword OUTPUT.
 
CREATE PROCEDURE [dbo].[GetFruitName]
      @FruitId INT,
      @FruitName VARCHAR(30) OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
     
      SELECT @FruitName = FruitName
      FROM Fruits
      WHERE FruitId = @FruitId
END
 
 

HTML Markup

The HTML Markup consists of:
TextBox – For capturing FruitId.
Button – For fetching FruitName.
The Button has been assigned with an OnClick event handler.
Label – For displaying FruitName.
Enter FruitId:
<asp:TextBox ID="txtFruitId" runat="server" />
<asp:Button ID="btnSubmit" OnClick="Submit" Text="Submit" runat="server" />
<br />
<br />
<asp:Label ID="lblFruitName" runat="server" />
 
 

Namespaces

You will need to import the following namespaces.
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
 
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
 
 

Return Output parameter from Stored Procedure in ASP.Net

When the Submit button is clicked, first the Connection Striung is read from Web.Config file.
Note: For more details on how to read Connection String from Web.Config file please refer my article Read or Write Connection Strings in Web.Config file using ASP.Net using C# and VB.Net.
 
The SqlCommand class object is created and FruitId is added as parameter along with its Value i.e. the FruitId entered in the TextBox.
Then, the second parameter FruitName is added which is an Output parameter hence it cannot be added using AddWithValue function hence it is added using the Add method of SqlCommand class with its Data Type and Size specified.
Once the FruitName parameter is added, its Direction is set to Output since by default the Direction of all parameters are Input.
Then, the ExecuteNonQuery function of SqlCommand class is executed and the FruitName value is fetched using Value property of SqlParameter class.
Note: Note: For more details on ExecuteNonQuery method, please refer my article Understanding SqlCommand ExecuteNonQuery in C# and VB.Net.
 
Finally, the FruitName is displayed using Label control.
C#
protected void OnSubmit(object sender, EventArgs e)
{
     string spName = "GetFruitName";
     string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
     using (SqlConnection con = new SqlConnection(constr))
     {
         using (SqlCommand cmd = new SqlCommand(spName, con))
        {
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Parameters.AddWithValue("@FruitId", int.Parse(txtFruitId.Text.Trim()));
             cmd.Parameters.Add("@FruitName", SqlDbType.VarChar, 30);
             cmd.Parameters["@FruitName"].Direction = ParameterDirection.Output;
             con.Open();
             cmd.ExecuteNonQuery();
             con.Close();
             string fruitName = cmd.Parameters["@FruitName"].Value.ToString();
             lblFruitName.Text = "Fruit Name: " + fruitName;
         }
     }
}
 
VB.Net
Protected Sub OnSubmit(sender As Object, e As EventArgs)
     Dim spName As String = "GetFruitName"
     Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
     Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand(spName, con)
             cmd.CommandType = CommandType.StoredProcedure
             cmd.Parameters.AddWithValue("@FruitId", Integer.Parse(txtFruitId.Text.Trim()))
             cmd.Parameters.Add("@FruitName", SqlDbType.VarChar, 30)
             cmd.Parameters("@FruitName").Direction = ParameterDirection.Output
             con.Open()
             cmd.ExecuteNonQuery()
             con.Close()
             Dim fruitName As String = cmd.Parameters("@FruitName").Value.ToString()
             lblFruitName.Text = "Fruit Name: " & fruitName
         End Using
    End Using
End Sub
 
 

Screenshot

How to return Output parameter from Stored Procedure in ASP.Net using C# and VB.Net
 
 

Downloads

Download Code