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

c# entity-framework-6 sql-server

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!

1
4
11/8/2014 11:16:02 PM

Accepted Answer

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

9
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.

Solution

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





Related

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