Retrieve Store Procedure’s Output parameter by C# (

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.


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.

Please share, if it is helpfulShare on FacebookShare on LinkedInTweet about this on TwitterShare on Google+Email this to someonePrint this page

3 thoughts on “Retrieve Store Procedure’s Output parameter by C# (

  1. I just want to say I am all new to blogging and actually enjoyed your web page. More than likely I’m going to bookmark your site . You amazingly have superb stories. Regards for sharing your website page.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>