EF6 - ExecuteSqlCommandAsync - Get return parameter (declare scalar variable error)

Question

I have the following code:

object[] parameters =
    {
        new SqlParameter("@val1", val1),
        new SqlParameter("@val2", val2),
        new SqlParameter
        {
            ParameterName = "@retVal",
            SqlDbType = SqlDbType.Int,
            Direction = ParameterDirection.ReturnValue,
            Value = -1
        }
    };

    await context.Database.ExecuteSqlCommandAsync("EXEC @retVal = Example_SP @val1, @val2", parameters);

The SP I'm using is fine and returns a value in SQL MS fine. But when I execute it using EF I am told I 'must declare the scalar variable @retVal'. Isn't that what my SqlParameter does??

I've tried removing the '@' sign form the parameters, as some have suggested elsewhere, but as I understand it the '@' sign is optional and makes no difference anyway.

How do I get the return value from the SP without causing errors, using ExecuteSqlCommandAsync?

Thank you!

Answer 1

You need to use ParameterDirection.Output instead of ParameterDirection.ReturnValue.