How to get output from stored procedure using ASP.Net


Few days ago found a member in ASP.Net forum complaining about, he don’t found any clear example to get stored procedure output parameter from c# code in the internet.

So I provide him the code and think to write it here ,For helping others. Its very straight forward I am giving the stored procedure here it will help to understand how to write storedprocedure to return output:

Create procedure PROC_ReturnValue
(
@id int,
@Name_Id varchar(100) Output
)
As

set @Name_Id=(Select (Convert(varchar(20),id)+name)
from Items where id=@id)

And the following c# code shows how to call the storedprocedure along with the parameter(also the output parameter). And after execution  of the stored procedure how to get the output value:

C# Code :

SqlConnection cn = new SqlConnection(“data source=dbServerName;database=dbTest;uid=admin;pwd=Pass@123”);
SqlCommand cm = new SqlCommand();
cm.Connection = cn;

cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = “PROC_ReturnValue”;
cm.Parameters.AddWithValue(“@id”, 6);

     //Adding the output parameter
        cm.Parameters.AddWithValue(“@Name_Id”,string.Empty);
        cm.Parameters[1].SqlDbType = SqlDbType.VarChar;
        cm.Parameters[1].Direction = ParameterDirection.Output;
        cm.Parameters[1].Size = 100;

cn.Open();

SqlDataReader dr= cm.ExecuteReader();

        //Accessing the output parameter value

      string sOutPut= cm.Parameters[1].value;
cn.Close();

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: