In this article I will explain how to use and return value from Stored Procedure using Output Parameter in ASP.Net.
For this article I a Table named Fruits is used which contains FruitId and FruitName columns. The name of the Fruit is fetched using Output Parameter in SQL Server Stored Procedure in ASP.Net
 
Database
For this example I have created a database named FruitsDB which has a table named Fruits with the schema as follows.
Return Output parameter from Stored Procedure in ASP.Net
 
In the Fruits Table I have inserted few records as shown below
Return Output parameter from Stored Procedure in ASP.Net
 
Connection String
Following is the connection string defined in the Connection Strings section of the Web.Config. You need to modify it as per your SQL Server Instance and Database name.
<connectionStrings>
 <addname="constr"connectionString="Data Source=.\SQL2005;Initial Catalog=FruitsDB;User id = sa;password=pass@123"/>
</connectionStrings>
 
 
HTML Markup
The HTML Markup consists of an ASP.Net TextBox using which the Fruit Id will be passed to the SQL Server Stored Procedure, an ASP.Net Label within which the name of the Fruit fetched using Output Parameter will be displayed and finally an ASP.Net Button control to trigger the process of fetching the Fruit Name.
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" />
 
 
Stored Procedure
The SQL Server Stored Procedure accepts the following two Parameters
1. FruitId – This is an INPUT Parameter used to pass the Id of the Fruit.
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.
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetFruitName]
      @FruitId INT,
      @FruitName VARCHAR(30) OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
     
      SELECT @FruitName = FruitName
      FROM Fruits
      WHERE FruitId = @FruitId
END
 
 
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
The following event handler is executed when the Button is clicked, it simply makes a database call to the stored procedure GetFruitName (discussed earlier).
First the Input Parameter @FruitId is added along with its Value i.e. the Fruit Id entered in the TextBox using AddWithValue method of SqlCommand Parameter class.
Next the second Parameter @FruitName is added. Since @FruitName is an Output Parameter we cannot use AddWithValue function hence it is added using the Add method of SqlCommand Parameter class with its Data Type and Size specified.
Once the @FruitName Parameter is added, then its Direction is set to Output since by default the Direction of all Parameter is Input.
Once the Stored Procedure is executed, the value fetched from the Stored Procedure is stored in the Value property of the @FruitName Output Parameter.
Finally the name of the Fruit is displayed on page using Label control.
C#
protected void Submit(object sender, EventArgs e)
{
    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("GetFruitName", 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();
            lblFruitName.Text = "Fruit Name: " + cmd.Parameters["@FruitName"].Value.ToString();
        }
    }
}
 
VB.Net
Protected Sub Submit(sender As Object, e As EventArgs)
    Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constring)
        Using cmd As New SqlCommand("GetFruitName", 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()
            lblFruitName.Text = "Fruit Name: " & cmd.Parameters("@FruitName").Value.ToString()
        End Using
    End Using
End Sub
 
Return Output parameter from Stored Procedure in ASP.Net
 
Downloads