Why is stored procedure ReturnValue null in C# when it has value in SQL?

Question

I have a stored procedure in SQL server that returns 0 at the end as shown:

create procedure myProc
as
begin
    select * from sampleTable

    return 0
end

This value is returned as expected when calling the stored procedure from sql:

exec @i = myProc
select @i -- value is 0

I call the procedure in C# like this:

SqlDataReader dr = await CommandObj.ExecuteReaderAsync(CommandBehavior.SequentialAccess, token).ConfigureAwait(false);

However, when attempting to read this value in C#, the value of the parameter with ParemeterDirection.ReturnValue is null.

Interestingly, the code above works for procedures that do not select a datatable. Sample code that works:

await CommandObj.ExecuteNonQueryAsync(token).ConfigureAwait(false);

followed by same code to read the ReturnValue. I would like to specifically use a ReturnValue, not an out parameter.

Any ideas?

If you want a value returned from a stored procedure you should use an output parameter. The return value is used to indicate the status of the execution. — Sean Lange, Jul 26 '23 at 18:59
You need to do `while (reader.NextResult()) { }` and then close the reader first, before trying to retrieve any output or return parameters — Charlieface, Jul 26 '23 at 19:13
@SeanLange `OUTPUT` still won't solve the problem, which is that the setting of the parameter hasn't happened until the reader stream has finished. — Charlieface, Jul 26 '23 at 19:14
Thank you @Charlieface, I added `await dr.CloseAsync();` (after my existing code that reads the result) and that solved it! If this question is reopened I'd accept that as the answer. — Rachel, Jul 28 '23 at 13:46
It's the same answer to a slightly different question. In my case, `out` parameters were working as expected. — Rachel, Jul 28 '23 at 14:32

score 1 · Answer 1 · answered Jul 26 '23 at 18:32

1

I don't see your other parts of your code, but do you add parameter for the return value before executing the command?

SqlParameter returnParam = new SqlParameter();
returnParam.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(returnParam);

answered Jul 26 '23 at 18:32

Max Naumov

I'm using derived parameters, so this is not the answer. I had neglected to close the DataReader as commented above. – Rachel Jul 28 '23 at 13:47

1 Answers1