Get returned value from Stored Procedure

This example shows you how to get returned value from Stored Procedure.First of all create a stored procedure which returns a value.In this example of stored procedure it receives a parameter named "uname".
Then its getting the Id of user from function fn_getUserBD_ID and returns that value.

Create PROCEDURE sp_GetBDID

@uname varchar(25)=null

AS

 declare @dbBD_id bigint

BEGIN

    SET NOCOUNT ON;

    Select @dbBD_id=dbo.fn_getUserBD_ID(@uname)

    return @dbBD_id

END

Now to get the value write this code in code behind

  public void GetBDID()

    {

        string st = Session["BDGen"].ToString();

        SqlCommand cmd = new SqlCommand("sp_GetBDID", con);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@uname", st);

        // Return value as parameter

        SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);

        returnValue.Direction = ParameterDirection.ReturnValue;

        cmd.Parameters.Add(returnValue);

        if (con.State == ConnectionState.Closed)

            con.Open();

        cmd.ExecuteNonQuery();

        con.Close();

        lblUId.Text = returnValue.Value.ToString();

    }