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

c# entity-framework-6 sql-server


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!

11/8/2014 11:16:02 PM

Accepted Answer

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

12/26/2016 11:21:39 AM

Popular Answer

TL&DR: Create an Output Variable to house the 'returning' ID query. This answer is related to a POSTGRES database.

Rationale: The ExecuteSqlCommand function returns the number of rows affected. You need an additional output which is the 'returning' id that is inserted. Therefore, you need to provide an OUTPUT parameter, which can hold this value.


Use the System.Data.ParameterDirection.Output for the Direction within NpgsqlParameter.

  NpgsqlParameter idOut = new NpgsqlParameter
            Direction = System.Data.ParameterDirection.Output
  await _context.Database.ExecuteSqlCommandAsync($"INSERT INTO mytable (myid, create_time, modified_time) VALUES ({mytableid}, now(),now()) RETURNING myid;", idOut);

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow