Retrieve Store Procedure’s Output Parameter By C# (ASP.Net)

1 minute read

How to retrieve store procedure’s output parameter by C#? Here I have explained with a simple example. I created a simple store procedure with output parameter. Then I catch it from code behind page of and displayed it to a label. Here I used a class DBConnector to connect with database. Lets look on the demo.

Step 1: Set connection string in web.config.

     <add connectionstring="Data Source=SOFT;Initial Catalog=TestDB;User ID=sa;Password=sa" name="SQLServerConnectionString" providername="System.Data.SqlClient">

Step 2: Create a store procedure with output parameter.

CREATE PROCEDURE [dbo].[sp_output_param]
     @input_param VARCHAR(200)
    , @Response VARCHAR(250) OUTPUT
    --DECLARE @myName
    SET @Response = 'Welcome ' + @input_param

Step 3: Create a DBConnector Class to retrieve data from database.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public class DBConnector
    private string connectionString = null;
    private SqlConnection sqlConn = null;
    private SqlCommand cmd = null;
    public DBConnector()
        connectionString = ConfigurationManager.ConnectionStrings["SQLServerConnectionString"].ToString();
    public SqlCommand GetCommand()
        cmd = new SqlCommand();
        cmd.Connection = sqlConn;
        return cmd;
    public SqlConnection GetConn()
        sqlConn = new SqlConnection(connectionString);
        return sqlConn;

Step 4 : Create a label name lblMsg in asp page. Then in a button event, write the following code. You will see your desired output in the lebel which is actually the value of output parameter. Here btnGetOutputParam_Click is the click event of Button btnGetOutputParam.

protected void btnGetOutputParam_Click(object sender, EventArgs e)
        SqlConnection sqlConn;
        SqlCommand cmd;
        DBConnector objDBConnector = new DBConnector();
        sqlConn = objDBConnector.GetConn();
        cmd = objDBConnector.GetCommand();
        SqlDataReader rdr = null;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_output_param";
        cmd.Parameters.AddWithValue("@input_param", "Mahedee");
        cmd.Parameters.Add("@Response", SqlDbType.VarChar, 250);
        cmd.Parameters["@Response"].Direction = ParameterDirection.Output;
            if (sqlConn.State == ConnectionState.Closed)
            rdr = cmd.ExecuteReader();
            string outputValue = cmd.Parameters["@Response"].Value.ToString();
            this.lblMsg.Text = outputValue;
        catch (Exception exp)
            throw (exp);
            if (sqlConn.State == ConnectionState.Open)

You will see in label - Welcome Mahedee which is provided by output parameter of store procedure.