In this article, I will explain you how to call stored procedure with Output Parameter using ado.net code with asp.net. First, we need to create a table with three columns EmpId, Name, Gender. We will supply Male or Female as input parameter value and return count of Male or Female employees as output parameter value based on input parameter.

Database Table :

Below is the script to create table and insert some dummy records.

create table tblEmployee
(
[EmpId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](20) NULL,
[Gender] [varchar](10) NULL
)

insert into tblEmployee values('Rahul','Male')
insert into tblEmployee values('Mary','Female')
insert into tblEmployee values('John','Male')
insert into tblEmployee values('Mathew','Male')

Stored Procedure :

Below is the stored procedure with two parameter. @Gender input parameter and @GenderCount is the output parameter which will return count of Male of Female employee. You can use OUTPUT or OUT keyword to return value.

create procedure proc_Employee
@Gender varchar(10)=null,
@GenderCount int output
as
begin
select @GenderCount=COUNT(*) from tblEmployee where Gender=@Gender
end

Designer File :

In designer file create a DropDownList control and Label control as shown below.

asp:DropDownList ID="ddlGender" runat="server" AutoPostBack="true"
OnSelectedIndexChanged="ddlGender_SelectedIndexChanged">
asp:ListItem>Selectasp:ListItem>
asp:ListItem>Maleasp:ListItem>
asp:ListItem>Femaleasp:ListItem>
asp:DropDownList>br />
asp:Label ID="lblCount" runat="server">asp:Label>

C# Code to call stored procedure with output parameter using Ado.net

Below is the C# code to get male or female employee count. Set @GenderCount parameter's direction property to ParameterDirection.Output. Once the procedure is executed, you can get the value of @GenderCount using Value property.

protected void ddlGender_SelectedIndexChanged(object sender, EventArgs e)
{
if (ddlGender.SelectedIndex != 0)
    {
string ConnectionString = "Server=xxxx; database=xxxx; user id=xxxx; password=xxxx";

        SqlConnection con = new SqlConnection(ConnectionString);

        SqlCommand cmd = new SqlCommand("proc_Employee", con);
        cmd.CommandType = CommandType.StoredProcedure;

// pass input parameter
        cmd.Parameters.AddWithValue("@Gender", ddlGender.SelectedItem.Text);

        cmd.Parameters.Add("@GenderCount", SqlDbType.Int);
        cmd.Parameters["@GenderCount"].Direction = ParameterDirection.Output;

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

string GenderCount = cmd.Parameters["@GenderCount"].Value.ToString();

        lblCount.Text =   ddlGender.SelectedItem+" count is " +GenderCount;
    }
}

Below is the another way to add OUTPUT parameter.

// pass input parameter
cmd.Parameters.AddWithValue("@Gender", ddlGender.SelectedItem.Text);

SqlParameter outputParameter = new SqlParameter();
outputParameter.ParameterName = "@GenderCount";
outputParameter.SqlDbType = SqlDbType.Int;
outputParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outputParameter);

con.Open();
cmd.ExecuteNonQuery();
con.Close();

string GenderCount = outputParameter.Value.ToString();

lblCount.Text = ddlGender.SelectedItem + " count is " + GenderCount;