[2.0] ADO.Net Retun Codes

[Bombdrop]

I have been playing about with obtaining return values from a stored procedure. the sp looks like so

CREATE PROCEDURE [proc_CustomersLoadByPrimaryKey]
(
    @CustomerID nchar(5)
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @Err int
if @CustomerID = 'a'
begin
set @err=99
return  @err
end
    SELECT
        [CustomerID],
        [CompanyName],
        [ContactName],
        [ContactTitle],
        [Address],
        [City],
        [Region],
        [PostalCode],
        [Country],
        [Phone],
        [Fax]
    FROM [Customers]
    WHERE
        ([CustomerID] = @CustomerID)

    SET @Err =74-- @@Error

    RETURN @Err
END

my code to access this via C# looks like so.

     private void button1_Click(object sender, EventArgs e)
        {
          
            SqlConnection con = new SqlConnection();
            con.ConnectionString = connectionString;
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = "proc_CustomersLoadByPrimaryKey";

            SqlParameter retParam = new SqlParameter();
            retParam.Direction = ParameterDirection.ReturnValue;
            retParam.SqlDbType = SqlDbType.Int;
            retParam.ParameterName = "@ret";
            retParam.Value = 0;

            SqlParameter Param = new SqlParameter("@CustomerID", SqlDbType.NChar, 5);
            Param.Direction = ParameterDirection.Input;
            Param.Value = "a";
        

            com.Parameters.Add(retParam);
            com.Parameters.Add(Param);
            con.Open();

            SqlDataReader red;
            red = com.ExecuteReader(CommandBehavior.CloseConnection);



            int returnValue = (int)com.Parameters["@ret"].Value;
            MessageBox.Show(returnValue.ToString());

        }

Now if I pass 'a' into the sp I get the desired return code of 99 but if i pass any other value such as '"ALFKI" a vaild Client I still wish to see a return code this time of value 74 but I do not receive a return code anyone know what i'm doing wrong?

Many thanks

[mendhak]

Do a return only at the end of the stored proc. Use your IF block just to set the value of @err.

[Bombdrop]

Thanks for the reply but I wish to more complicate Stored Procs than the one above that will do several checks on the data and thus could fail for several reasons and I wish to have a way of knowing why it failed.

eg
return -10 (no record)
return -20 (Recond has been update before you)

etc..

I use to be able to achive this in VB6 and good old ADO2.8

[mendhak]

I don't see how that is any different here.