CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
Jack Thor
Jack Thor

Reputation: 1604

Get return value from from SQL using execute reader

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.

Upvotes: 3

Views: 14565

Answers (1)

user270576
user270576

Reputation: 997

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

Upvotes: 7

Related Questions