Stored Procedure returns incorrect scalar value of -1, instead of return value

entity-framework entity-framework-6 stored-procedures

Question

I am trying to return a scalar value from a stored procedure. I actually want to return the ID of a newly created record, but I have simplified my problem down to a stored procedure that takes an int and attempts to return that same int. This always returns -1. Thank you very much for your help.

Web API Controller call

var idtest = dbconn.my_return_int(123);

The stored procedure:

ALTER PROCEDURE [dbo].[my_return_int]  
    @ID int  
AS  
BEGIN  
    SET NOCOUNT ON;  
    DECLARE @return as int

    SET @return = -999

RETURN @return
END   

The Context generated stored procedure call

public virtual int my_return_int(Nullable<int> iD)  
{  
    var iDParameter = iD.HasValue ?
        new ObjectParameter("ID", iD) :
        new ObjectParameter("ID", typeof(int));

    return (IObjectContextAdapter)this).ObjectContext.ExecuteFunction("my_return_int", iDParameter);  
}  
1
5
1/15/2015 12:01:44 PM

Popular Answer

When you execute ObjectContext.ExecuteFunction the result is:

from MSDN: discards any results returned from the function; and returns the number of rows affected by the execution

I.e. it doesn't return the output parameter, because it doesn't know there is one. Besides, as you have called SET NOCOUNT ON; in your stored procedure, it doesn't even return the number of affected rows, thus you get the -1.

So, you must do two changes:

  1. change your procedure so that it doesn't return the value, but selects it, i.e. instead of RETURN @return do SELECT @return AS alias. NOTE that you need the "AS alias" part. The alias can be whatever column name you want.
  2. change the mapping of the stored procedure, so that it expects an int32 value. If you have doubt, see this link.

In this way you'll read the return value as a result set, instead of a return value.

6
6/24/2019 10:36:48 PM


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