Stored procedure returns -1 when executed

Question

I have a stored procedure in SQL server 2008. It takes an email as parameter and then looks up if the user linked to it has rights to access a mobile MVC 5 application. The stored procedure returns either a 1 or a 0. Here is the code for the stored procedure.

-- Add the parameters for the stored procedure here

    @email varchar(128)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

DECLARE @Check int

SELECT  

    @Check = (
                    SELECT P.MobileRights
                    FROM eMK_Person as P
                    WHERE P.ID = (SELECT FK_Person FROM eMK_contact WHERE eMK_Contact.Information = @Email)
                    )

IF (@Check is null) RETURN 0
RETURN (@Check)

END

GO

In the standard mvc5 code of the account controller I added a simple check on this stored procedure, if the outcome is 0 it should just return the view. Here is a the code that I added to the Login task action result:

var test = db.SP_MB_MobileRights(model.Email);
if (db.SP_MB_MobileRights(model.Email) == 0)
{
    return View(model);
}

I have two test accounts. One that has the rights and one that does not. If I execute the stored procedure in the management studio with a query it works like it should, returning 0 for one and 1 for the other.

But when I debug the code the test variable always gets filled with -1.

Here is the code that MVC made for me when I added the stored procedure to the model.

public virtual int SP_MB_MobileRights(string email)
{
    var emailParameter = email != null ?
        new ObjectParameter("email", email) :
        new ObjectParameter("email", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("SP_MB_MobileRights", emailParameter);
}

When I debug through it, I can clearly see that the emailParameter variable is filled with the email address:

Screen shot of debugged code

I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Jun 16 '15 at 07:44
Ah sorry, I wanted to point out that the procedure returns -1 when executed in MVC, wasn't sure on how to do it. Will remember now, thanks! — Robin, Jun 16 '15 at 07:46
But the procedure has no idea whether you're running in MVC or not! I notice that the vast majority of your questions start with tags. Please don't do that anymore, ok? — John Saunders, Jun 16 '15 at 07:48
Try this link [http://stackoverflow.com/questions/27596869/store-procedure-not-return-value-in-mvc5][1] [1]: http://stackoverflow.com/questions/27596869/store-procedure-not-return-value-in-mvc5 — ANJYR, Jun 17 '15 at 13:58
Try this link [http://stackoverflow.com/questions/27596869/store-procedure-not-return-value-in-mvc5][1] [1]: http://stackoverflow.com/questions/27596869/store-procedure-not-return-value-in-mvc5 — ANJYR, Jun 17 '15 at 13:59
Try this link [http://stackoverflow.com/questions/27596869/store-procedure-not-return-value-in-mvc5][1] [1]: http://stackoverflow.com/questions/27596869/store-procedure-not-return-value-in-mvc5 — ANJYR, Jun 17 '15 at 14:02

Fred · Accepted Answer · 2015-06-16T08:00:54.403

0

If the procedure is meant to return a 1 or 0 try changing your procedure to:

AS
BEGIN
IF EXISTS (SELECT P.MobileRights FROM eMK_Person as P WHERE P.ID = (SELECT FK_Person FROM eMK_contact WHERE eMK_Contact.Information = @Email))
RETURN 1
ELSE
RETURN 0
END

You should also change your query to a JOIN rather then a sub query.

Something like:

SELECT P.MobileRights FROM eMK_Person as P  
     JOIN eMK_contact as C ON c.FK_Person = P.ID WHERE C.Information = @Email

edited Jun 16 '15 at 08:00

answered Jun 16 '15 at 07:50

Fred

5,663
4
45
74

I am not that experienced with SQL so sorry if this is a stupid question, but what benefits would a join have ? – Robin Jun 16 '15 at 07:54
@Robin This explains it for you. http://stackoverflow.com/questions/2577174/join-vs-sub-query In short using a join is generally faster. – Fred Jun 16 '15 at 07:57
Thanks you for the info! – Robin Jun 16 '15 at 07:58

Stored procedure returns -1 when executed

1 Answers1