Entityframework Storedprocedure recover parameter output

Question

How to recover the Output parameter of a storedProcedure?

SP Example:

create PROCEDURE StoreProcedure_Name 
    @ParametterWithNummvalue    varchar(50) = null, 
    @In_Parameter varchar(50), 
    @Out_Parameter varchar(10) out
AS

    set @Out_Parameter = 'teste'

RETURN 0

Code C#

SqlParameter[] Params = {
                new SqlParameter("@ParametterWithNummvalue", DBNull.Value),
                new SqlParameter("@In_Parameter", "teste novo"),
                new SqlParameter("@Out_Parameter", SqlDbType.VarChar, 10) { Direction = ParameterDirection.Output }};

            var r = Db.Database.ExecuteSqlCommand("exec StoreProcedure_Name @ParametterWithNummvalue, @In_Parameter, @Out_Parameter", Params);

            var ReturnValue = Params[2].Value;

Returnvalue should contain the "test" value but is returning empty.

Answer 1

Hello, try it this way:

//Separe os parâmetros de entrada e saída
var parametterWithNummvalue = new SqlParameter();
parametterWithNummvalue.ParameterName = "@ParametterWithNummvalue";
parametterWithNummvalue.Direction = ParameterDirection.Input;
parametterWithNummvalue.SqlDbType = SqlDbType.VarChar;
parametterWithNummvalue.Size = 50;
parametterWithNummvalue.Value = null; //Substitua pelo valor desejado

var inParameter = new SqlParameter();
inParameter.ParameterName = "@InParameter";
inParameter.Direction = ParameterDirection.Input;
inParameter.SqlDbType = SqlDbType.VarChar;
inParameter.Size = 50;
inParameter.Value = null; //Substitua pelo valor desejado

var outParameter = new SqlParameter();
outParameter.ParameterName = "@OutParameter";
outParameter.Direction = ParameterDirection.Output;
outParameter.SqlDbType = SqlDbType.VarChar;
outParameter.Size = 10;
outParameter.Value = null; //Substitua pelo valor desejado

var valorRetornado = new SqlParameter();
valorRetornado.ParameterName = "@ValorRetornado";
valorRetornado.SqlDbType = SqlDbType.Int;
valorRetornado.Direction = ParameterDirection.Output;

//Chame a procedure com base nos parâmetros criados
var dados = Db.Database.SqlQuery("exec @ValorRetornado = StoreProcedure_Name @ParametterWithNummvalue, @InParameter, @OutParameter OUT", valorRetornado, parametterWithNummvalue, inParameter, outParameter);

Obs: Note that the way your precedent was created in the example, will return two values, one in OUT (variable: outParameter) and another in RETURN (variable: valueRetornado).

I hope I’ve helped.

Answer 2

I managed to solve just added expression Out after the parameter outParameter (tomcunha’s answer)

In the end it was like this:

SqlParameter[] Params = {
    new SqlParameter("@ParametterWithNummvalue", DBNull.Value),
    new SqlParameter("@In_Parameter", "teste novo"),
    new SqlParameter("@Out_Parameter", SqlDbType.VarChar, 10) { Direction = ParameterDirection.Output }};

var r = Db.Database.ExecuteSqlCommand("exec StoreProcedure_Name @ParametterWithNummvalue, @In_Parameter, @Out_Parameter Out", Params);

var ReturnValue = Params[2].Value;