construct a stored procedure to return value to feed Entity Framework

Question

CREATE FUNCTION ValidateFRequirementFunction
(
    -- Add the parameters for the function here
    @SubProgID  nvarchar
)
RETURNS   decimal
AS
BEGIN
    -- Declare the return variable here
    DECLARE @RequirementLeft As decimal(10) ;
    -- Add the T-SQL statements to compute the return value here

    SET @RequirementLeft = ( SELECT f.FRequirementLeft
    FROM dbo.Fund f
    INNER JOIN dbo.Programme prog ON f.ProgID=prog.ID
    INNER JOIN dbo.Programme prog2 ON prog.ID=prog2.ParentID
    WHERE prog2.ID=@SubProgID  )
    -- Return the result of the function

    SELECT @RequirementLeft
END    

GO

here is the code for the stored procedure i have tried to execute, but it generates an error saying "Select statements included within a function cannot return data to a client." and "The last statement included within a function must be a return statement"

what is i want to get is to return a single decimal variable to entity framework. since i have seen in different article that i have to use select instead of return, i used select at the end

please help ! thanks

    

Answer 1

CREATE FUNCTION ValidateFRequirementFunction
(
    -- Add the parameters for the function here
    @SubProgID  nvarchar
)
RETURNS   decimal
AS
BEGIN
    -- Declare the return variable here
    DECLARE @RequirementLeft As decimal(10) ;
    -- Add the T-SQL statements to compute the return value here

    SET @RequirementLeft = ( SELECT f.FRequirementLeft
    FROM dbo.Fund f
    INNER JOIN dbo.Programme prog ON f.ProgID=prog.ID
    INNER JOIN dbo.Programme prog2 ON prog.ID=prog2.ParentID
    WHERE prog2.ID=@SubProgID  )
    -- Return the result of the function

    SELECT @RequirementLeft
END    

GO

here is the code for the stored procedure i have tried to execute, but it generates an error saying "Select statements included within a function cannot return data to a client." and "The last statement included within a function must be a return statement"

what is i want to get is to return a single decimal variable to entity framework. since i have seen in different article that i have to use select instead of return, i used select at the end

please help ! thanks

    

try this,

CREATE FUNCTION ValidateFRequirementFunction
(
    -- Add the parameters for the function here
    @SubProgID  nvarchar
)
RETURNS   decimal
AS
BEGIN
    -- Declare the return variable here
    DECLARE @RequirementLeft As decimal(10) ;
    -- Add the T-SQL statements to compute the return value here

    SET @RequirementLeft = ( SELECT f.FRequirementLeft
    FROM dbo.Fund f
    INNER JOIN dbo.Programme prog ON f.ProgID=prog.ID 
    INNER JOIN dbo.Programme prog2 ON prog.ID=prog2.ParentID
    WHERE prog2.ID=@SubProgID  )
    -- Return the result of the function

return

END    

GO

what exactly DECIMAL doing?

---

ANK HIT

- if reply helps, please mark it as ANSWER or helpful post

Answer 2

Change "select @RequirementLeft" to "return @RequirementLeft".  Generally, you can find good examples of any syntax usage in BOL. In addition, pay attention to your terminology.  You said "stored procedure" when you are actually creating a user-defined function (UDF).  If your issue is that your entity framework cannot use a UDF, then you need to create a stored procedure - which is done with "create procedure" and not with "create function". 

While we're at it, do NOT declare string-type values without specifying the length.  In this context, your argument is exactly one character in length.  It is also unusual for a column whose name ends in "ID" to be alphanumeric; the datatype for arguments and variables should generally be the same as the columns against which they are compared/retrieved.  This comment applies to both @subProgID as well as the function return value (which you declared as decimal while the local variable inside the function is declared as decimal(10) ).  You will avoid many subtle errors in you are consistent in your datatype usage. 

Answer 3

@ ank : thanks for your reply , i have changed everything as scott_morris-ga suggested to a stored procedure and it is successfully executed

@ scott_morris-ga:thanks for your reply, sorry for the miss use of terminologies that i am new to programming ...... i would appreciate if you recommend me what to read to master about stored procedure + entity frameworks

i used nvarchar(128) for the id column of the Fund table, b/c i wanted to use GUID to generate the id at run time in my code.

i have written a new stored procedure as per your advice and it run successfully . i even checked it with an input argument and the return value is correct. however when i tried to access the stored procedure using the context class of the entity framework , it always catches an exception ... i couldn't figure it out why

it raises the exception when it reaches the line with the code  "context.ValidateFRequirementProcedure(subprogid);"

Code for the Stored Procedure

-------------------------------------------------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[ValidateFRequirementProcedure]
    -- Add the parameters for the stored procedure here
    @SubProgID nvarchar (128)  
      
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @RequirmentLeft As decimal(10) ;
    -- Insert statements for procedure here
    SELECT @RequirmentLeft = f.FRequirementLeft
    FROM dbo.Fund f
    INNER JOIN dbo.Programme prog ON f.ProgID=prog.ID
    INNER JOIN dbo.Programme prog2 ON prog.ID=prog2.ParentID
    WHERE prog2.ID=@SubProgID
    
    RETURN @RequirmentLeft
END
-----------------------------------------------------------------------------------------------------------------------------------------------------

Code Snippet from VS

-----------------------------------------------------------------------------------------------------------------------------------------------

try
            {
                using(context=new SPIFEntities())
                {
                    var outputParameter = new System.Data.Objects.ObjectParameter("RequirementLeft", typeof(decimal));
                   context.ValidateFRequirementProcedure(subprogid); // only works for sub programs b/c the procedure tries to filter by subprog id and return the frequirmentleft from the parent table
                   if (outputParameter.Value != null) //trying to convert NULL value raises exception

                       return Convert.ToDecimal(outputParameter.Value);
                }
                return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }

----------------------------------------------------------------------------------------------

Answer 4

Can you elaborate on what you're trying to achieve? Decimal(10) type does not make much sense as it doesn't contain the precision and you can use bigint instead.

You can only return int value from the stored procedure and usually it's used for returning the error code.

If you want to use output parameter (as in your C# code), then you need to declare OUTPUT parameter for the stored procedure and return the decimal value through the output parameter.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

what i really want to achieve is , i want to retrieve a decimal value (@RequirmentLeft ) using the stored procedure and then i want to access that value with context object of the Entity Framework. I have used the output parameter in my code thinking that is the only way to grab returned value from stored procedure. I would appreciate if you can recommend any other simpler way. thanks  

Answer 6

I think to return scalar value the best way is to use OUTPUT parameter of the stored procedure. You C# code will need to declare this parameter as ParameterDirection.Output and you will make a minor change in the stored procedure, e.g.

ALTER PROCEDURE [dbo].[ValidateFRequirementProcedure]
 (
    @SubProgID nvarchar (128),
    @RequirementLeft decimal(10,2) OUTPUT  
)      
AS
BEGIN
  
    SET NOCOUNT ON;
  
    SELECT @RequirmentLeft = f.FRequirementLeft
    FROM dbo.Fund f
    INNER JOIN dbo.Programme prog ON f.ProgID=prog.ID 
    INNER JOIN dbo.Programme prog2 ON prog.ID=prog2.ParentID
    WHERE prog2.ID=@SubProgID
    
    RETURN 0;
END

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog