I am having some problems capturing the return value from a SP after I excecute it. Here is the code I use:

SqlConnection oCon = new SqlConnection(this.ConnectionString);
SqlCommand cmd = new SqlCommand("mySP", oCon);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter oParam = cmd.Parameters.Add("@Id", SqlDbType.UniqueIdentifier);
oParam.Value = myID;
oCon.Open();
cmd.ExecuteNonQuery();
oCon.Close();

I have been raising exceptions within SPs to notify the client that something was not cosher, but I would also like to know how to evaluate the return value from a SP.

Let us assume that for this example the SP looks something like this:

UPDATE myTable SET Name = 'Einar' WHERE Id = @Id
RETURN @@ERROR