Stored procedure always returns -1 (asp.net MVC, Entity, SQL)

Question

I am attempting to call a stored procedure in SQL Server from a database first MVC Entity Framework project but the return values always returns -1.

I have tried MANY different ways to fix this and have been unsuccessful so far. I have listed what I believe to be my relevant code below. Let me know if more code is necessary. Thanks to anyone who can help!

This is just my test code to see if its working correctly.

public ActionResult Index()
{
    Cust_Entities cse = new Cust_Entities();

    if (cse.DoesCustomerNumberExist("1") == 1)
    {
        return RedirectToAction("Index", "Home");
    }
    else
    {
        return RedirectToAction("About", "Home");
    }          
}

public virtual int DoesCustomerNumberExist(string customer_Number)
{
    var customer_NumberParameter = customer_Number != null ?
        new ObjectParameter("Customer_Number", customer_Number) :
        new ObjectParameter("Customer_Number", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("DoesCustomerNumberExist", customer_NumberParameter);
}

SQL Server stored procedure code:

ALTER PROCEDURE [dbo].[DoesCustomerNumberExist]
      @Customer_Number VARCHAR(20)
AS
BEGIN
      SET NOCOUNT ON;

      DECLARE @Exists INT

      IF EXISTS(SELECT Customer_Number
                        FROM business_Final.dbo.AR_Customer
                        WHERE Customer_Number = @Customer_Number)
      BEGIN
            SET @Exists = '1'
      END
      ELSE
      BEGIN
            SET @Exists = '0'
      END

      RETURN @Exists
END

Side note: since @Exists is declared as an INT, you should NOT use single quotes (since you're dealing with a numeric value - not a string) when setting values - use SET @Exists = 1 - no single quotes, no unnecessary conversion from string to int etc. — marc_s
– marc_s, Commented Jul 13, 2017 at 16:57

RBarryYoung · Accepted Answer · 2017-07-13 16:53:25Z

3

You are using the untyped version of ExecuteFunction. According to the doc:

Executes a stored procedure or function that is defined in the data source and expressed in the conceptual model; discards any results returned from the function; and returns the number of rows affected by the execution.

If no modifications were made, this is typically -1.

Here's the doc on the different forms of ExecuteFunction: https://msdn.microsoft.com/en-us/library/system.data.objects.objectcontext.executefunction(v=vs.110).aspx

answered Jul 13, 2017 at 16:53

RBarryYoung

57k15 gold badges99 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris Pratt · Accepted Answer · 2017-07-13 16:53:55Z

2

The return value of the non-generic ExecuteFunction is the number of rows affected, not the return value of your stored procedure. In other words, it's only for things like updates, deletes, etc., not selects.

To return data, you must use the generic version, i.e. ExecuteFunction<TElement>, where TElement is the type of your return. This is due to the fact that the method needs to know how to bind the value(s) it gets back from SQL Server.

answered Jul 13, 2017 at 16:53

Chris Pratt

240k37 gold badges414 silver badges469 bronze badges

Collectives™ on Stack Overflow

Stored procedure always returns -1 (asp.net MVC, Entity, SQL)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related