5

I'm attempting to call a stored proc through EF and retrieve a return value from the stored proc. I've used this answer as a guide. Here is my stored proc:

CREATE PROCEDURE [dbo].[usp_test]
(
    @result int OUT
)
AS
BEGIN

--DO STUFF

SET @result = 0
END

Here is how I'm calling it from EF:

var status = new SqlParameter
{
    ParameterName = "result",
    DbType = DbType.Int32,
    Direction = ParameterDirection.Output 
};
var result = context.Database.SqlQuery<int>("EXEC usp_test @result", status);
var wasSuccessful = result.First() == 1;
if (!wasSuccessful)
{
    //DO STUFF
}

I'm getting this error message:

The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types

What am I doing wrong?

Community
  • 1
  • 1
mellis481
  • 4,332
  • 12
  • 71
  • 118
  • Did you do a SELECT in your stored procedure? If so this is your return type. As a hint - you must consume the resultset before you can get the value of the output parameter. – Pawel Feb 12 '16 at 00:20
  • @Pawel: The entire stored proc is in the question. I also tried `SET @result = 0`. – mellis481 Feb 12 '16 at 00:23
  • OK. `--DO STUFF` indicated you are doing something in the body and you don't set the value of the output parameter that's why I did not know if this is a complete procedure... – Pawel Feb 12 '16 at 00:25
  • @Pawel: Sorry for the confusion. Is what I'm doing now (updated) how you set an output parameter in the SP? – mellis481 Feb 12 '16 at 00:27
  • I don't remember at the moment but it might be that EF always expects a resultset. Can you add a dummy `SELECT 0` to your stored proc and see if it works? – Pawel Feb 12 '16 at 00:30
  • You can also change ParameterDirection.Output to ParameterDirection.ReturnValue then you don't need the select. – Grayson May 15 '17 at 17:49

5 Answers5

4

Try select @result before end of procedure.

CREATE PROCEDURE [dbo].[usp_test]
(
    @result int OUT
)
AS
BEGIN

--DO STUFF

SET @result = 0
Select @result
END

Hope it works.

Mojtaba
  • 514
  • 9
  • 19
1

I wanted to return the new id of an inserted row with a stored procedure. So I also made an OUTPUT parameter. In C# with EF6 I got it as following:

using System.Data.Entity.Core.Objects;
...
db = new myEntities1();
ObjectParameter returnId = new ObjectParameter("returnId", DbType.Int64);
db.postLogItem("New item.", returnId);
MessageBox.Show("Logitem added. New returnId: " + (int)returnId.Value);

The name of the variable in the stored procedure needs to match the one with calling the constructor of ObjectParameter.

CREATE PROCEDURE [dbo].[postLogItem]
    @description nvarchar(200),
    @returnId bigint OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.LogItem 
        (
            time,
            description
        )
        VALUES
        (
            GETDATE(),
            @description
        );

    SET @returnId = SCOPE_IDENTITY();
END
Pete
  • 1,191
  • 12
  • 19
0

Try adding .First(); to the end...

var result = context.Database.SqlQuery<int>("EXEC usp_test @result", status).First();

I don't this you need First() here either since you're returning a single value, this would only work with an IEnumerable - but double check.

var wasSuccessful = result == 1;
Christian Phillips
  • 18,399
  • 8
  • 53
  • 82
0

Change ParameterDirection.Output to ParameterDirection.ReturnValue

Grayson
  • 191
  • 1
  • 9
-3

If nothing works then you can try this alternative approach:

  1. Compute the result in PROC and save it in a table in database
  2. Read the data from that table in EF code.

However, if you have a concurrent system that can run many such EF codes in parallel then ofcourse your stored proc needs become more sophisticated to handle concurrency.

Thanks, hope this might help.

Jayesh Prakash
  • 141
  • 7