Get Stored Procedure Return Value

 SqlConnection con = null;
        try
        {
            string connString = ConfigurationManager.ConnectionStrings["cnTest"].ConnectionString;
            con = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_ValidateUser";
            cmd.Parameters.AddWithValue("@Name", txt1.Text);
            con.Open();
            // Return value as parameter
            SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(returnValue);

            // Execute the stored procedure
         
            cmd.ExecuteNonQuery();
            con.Close();

            Int32 ret= Convert.ToInt32(returnValue.Value);
            Response.Write(Convert.ToString(ret));
   
        }
        catch (SqlException ex)
        {
            // handle error
        }
        catch (Exception ex)
        {
            // handle error
        }
        finally
        {
            con.Close();
        }






Create Proc usp_ValidateUser
@Name Varchar(50)
AS 
BEGIN 
        if exists(Select Name from tblTestUser where Name=@Name)
         Begin
                return 1
         End
       
        else
         Begin
                return -1
         End
   
       
END