SQL Parameters - Input, Output, ReturnValue + From store procedure [C#]

Today, i will show you how to write a stored procedure to accept an input, return an output and also return a return value. I used adventureworks database

Here the sql to create a stored procedure

-- ================================================
-- KLIKBLIG.BLOGSPOT.COM
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE klikblig_getProductName
-- Add the parameters for the stored procedure here
@ProductNumber nvarchar(25),
@Name nvarchar(50) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

set @Name = ''

begin try
select @Name = p.name
from production.product p
where p.productnumber like @ProductNumber

if(@Name = '')
return 0
else
return 1
end try

begin catch
return -1
end catch

END
GO

Then, on default.aspx; i create a button, textbox and also a label (all id are default). On the default.cs.aspx paste the following code

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 partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;

conn.Open();
cmd.Connection = conn;
cmd.CommandText = "klikblig_getProductName";
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = new SqlParameter();

//@ProductNumber - INPUT
param.ParameterName = "@ProductNumber";
param.SqlDbType = SqlDbType.NVarChar;
param.Size = 25;
param.Direction = ParameterDirection.Input;

cmd.Parameters.Add(param);
cmd.Parameters["@ProductNumber"].Value = TextBox1.Text.Trim();
param = null;

//@Name - OUTPUT
param = new SqlParameter();
param.ParameterName = "@Name";
param.SqlDbType = SqlDbType.NVarChar;
param.Size = 50;
param.Direction = ParameterDirection.Output;

cmd.Parameters.Add(param);
param = null;

//@ReturnValue - RETURNVALUE
param = new SqlParameter();
param.ParameterName = "@ReturnValue";
param.SqlDbType = SqlDbType.Int;
param.Direction = ParameterDirection.ReturnValue;

cmd.Parameters.Add(param);
param = null;

reader = cmd.ExecuteReader();

reader.Read();

//GET THE RESULT
String name = "";
int returnValue = 0;

name = (String)cmd.Parameters["@Name"].Value;
returnValue = (int)cmd.Parameters["@ReturnValue"].Value;

if (returnValue == 0)
{
Label1.Text = "No Results Found";
}
else if (returnValue == 1)
{
Label1.Text = name;
}
else if (returnValue < 0)
{
Label1.Text = "Error";
}


}
}

You should get a result in the label