Get return value from stored procedure

Question

I'm using Entity Framework 5 with the Code First approach. I need to read the return value from a stored procedure; I am already reading output parameters and sending input parameters, but I don't know how to read the return value.

Is it possible?

Here is the code that I use to call the stored procedure:

var outParam = new SqlParameter();
outParam.ParameterName = "@StatusLog";
outParam.SqlDbType = SqlDbType.NVarChar;
outParam.Size = 4000;
outParam.Direction = ParameterDirection.Output;

var code = new SqlParameter();
code.ParameterName = "@Code";
code.Direction = ParameterDirection.Input;
code.SqlDbType = SqlDbType.VarChar;
code.Size = 20;
code.Value = "123";

var data = _context.Database.SqlQuery<Item>("exec spItemData @Code, @StatusLog OUT", code, outParam);

var result = data.FirstOrDefault();

Answer 1

I found it! I can read the return value with an output parameter that has to be used in this way:

// define a new output parameter
var returnCode = new SqlParameter();
returnCode.ParameterName = "@ReturnCode";
returnCode.SqlDbType = SqlDbType.Int;
returnCode.Direction = ParameterDirection.Output;

// assign the return code to the new output parameter and pass it to the sp
var data = _context.Database.SqlQuery<Item>("exec @ReturnCode = spItemData @Code, @StatusLog OUT", returnCode, code, outParam);

Answer 2

The solution provided by Daniele wasn't working for me, until I found this blog post from Diego Vega, which explains that:

You will need to read the whole results before you can access the values of output parameters (...) This is just how stored procedures work and not specific to this EF feature.

Also, in my case, I wasn't returning an entity, I only had to execute the stored procedure, so I replaced Item with object in _context.Database.SqlQuery<object>.

Here is the sample code:

var code = new SqlParameter("@Code", 1);

var returnCode = new SqlParameter("@ReturnCode", SqlDbType.Int);
returnCode.Direction = ParameterDirection.Output;

var outParam = new SqlParameter("@StatusLog", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;

var sql = "exec @ReturnCode = spSomeRoutine @Code, @StatusLog OUT";
var data = _context.Database.SqlQuery<object>(sql, returnCode, code, outParam);

// Read the results so that the output variables are accessible
var item = data.FirstOrDefault();

var returnCodeValue = (int)returnCode.Value;
var outParamValue = (int)outParam.Value;

Here's a sample stored procedure:

CREATE PROCEDURE [dbo].[spSomeRoutine]
    @Code Int,
    @StatusLog INT OUTPUT
AS
BEGIN
    SET @StatusLog = 5
    RETURN 10
END

Answer 3

In the case where the stored proc does not have an output parameter I do the following, effectively making some Sql that returns a select statement;

var data = context.Database.SqlQuery<int>(@"declare @num int
exec  @num = myStoredProcThatReturnsInt 
select @num");

var result = data.First();