Get return value from from SQL using execute reader

Question

So I have this stored procedure in SQL server that has this bit of SQL in it

... part of store procedure...
IF @@ERROR = 0 --AND @@ROWCOUNT = 1
BEGIN
     .. dO STUFF 
     SELECT * FROM MyTable
    RETURN 0
END
ELSE
BEGIN
    RAISERROR('Something went wrong :-(', 16, 1)
    RETURN -1
END

END

in my C# code where I get the data I do it like this

//Sql param used to get the return value from the store procedure
SqlParameter returnValueParam = command.Parameters.Add("@return_value", SqlDbType.Int);
returnValueParam.Direction = ParameterDirection.ReturnValue;
using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
 while (reader.Read())
 {
  SpRetrunValue.EmailAddress = DBNulls.DBNullToString(reader["Email"], string.Empty);
... More stuff
  }
   reader.NextResult();
   SpRetrunValue.ExternalData = new List<ExternalData>();
   var ExtData = new ExternalData();
  while (reader.Read())
  {
   ExtData.Id = DBNulls.DBNullToInteger(reader["ID"], 0);
   SpRetrunValue.ExternalData.Add(intExtData);
   }
   //get the return code on the SP 0 for success -1 for error
   SpRetrunValue.ResultCode = (int)returnValueParam.Value;
   }

the problem I am having is that if I use it with command.ExecuteNonQuery(); then I can get the return value. But using as is now I can not get the return value, but I do get the result set. Is it not possible to get the return value this way? I have seen this post here on stackoverflow, but that requires me adding another param to the stored procedure, which I feel defeats the purpose of just returning a value like in my stored procedure above.

Solution

You'll have to complete processing for all rowsets before attempting to capture the Return value or OUTPUT parameters. Place SpRetrunValue.ResultCode = (int)returnValueParam.Value; after your using statement.

Understanding SQL Server Return Codes and Output Parameters

Also, this post

Comments

[Sql Surfer]

That is a good article. Each example is different so look at the details. It is good to understand how the reader.Close() is affecting the return code to actually be there before you should read the value.