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);
}
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:
RETURN @return
do SELECT @return AS alias
. NOTE that you need the "AS alias" part. The alias
can be whatever column name you want.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.