SQL Server Why is stored procedure ReturnValue null in C# when it has value in SQL? [duplicate]

qjp7pelc 于 2023-08-02 发布在 C#

关注(0)|答案(1)|浏览(163)

This question already has answers here:

The community is reviewing whether to reopen this question as of 4 days ago.

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?

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);