1

I'm using Microsoft SQL Server 2014 and I have a stored procedure in my database called spLogin that returns a boolean to indicate whether or not login was successful.

This is the stored procedure:

USE [MyDataBase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spLogin]
@loginName NVARCHAR(50),
@password NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @bit BIT
    IF NOT EXISTS (SELECT TOP 1 User.UserID FROM User WHERE User.LoginName=@loginName)
        OR RTRIM(LTRIM(@loginName)) IS NULL OR RTRIM(LTRIM(@loginName)) IS NULL
        SET @bit=0
    ELSE
        BEGIN
            DECLARE @userID INT
            SET @userID= (SELECT User.UserID FROM User WHERE User.LoginName=@loginName
                              AND User.Password=HASHBYTES('SHA2_512', @password+CAST(User.Salt AS NVARCHAR(36))))
            IF(@userID IS NULL)
                SET @bit = 0
            ELSE
                SET @bit = 1
        END
    RETURN @bit
END

When I execute this stored procedure on Microsoft SQL Server 2014 Management Studio, it returns 1 if I pass the correct arguments. For example:

USE [MyDataBase]
GO
DECLARE @return_value int
EXEC    @return_value = [dbo].[spLogin]
        @loginName= N'adminCharles',
        @password= N'admin123'
SELECT  'Return Value' = @return_value
GO

In this case, it returns the value I expected, which is 1.

But, when I use Entity Framework on a WPF project, it returns -1 even though I'm passing the correct arguments. For instance:

DataBaseEntities context = new DataBaseEntities(); 
var login = context.spLogin("adminCharles","admin123");
//output: -1

In that case, it outputs -1 and I just simply don't know why.

I would really appreciate some help here. Thanks in advance.

And by the way, this is just a simple project for learnign Entity Framework.

EDIT

I changed @bit BIT for INT and at the end of the stored procedure, I wrote this:

SET @bit=0
RETURN @bit

I updated my project and I'm still getting -1. Could it be a permissions issue?

GianMS
  • 923
  • 2
  • 13
  • 25
  • 1
    Try `select @bit` instead of `return`. The - 1 is most likely the number of rows affected which is is - 1 since no count is off. You need to select a value not return one – pinkfloydx33 May 13 '17 at 09:00
  • Well, it works, but I'm still confused. I've read in a lot of posts that for returning a bit it's better to use RETURN rather than SELECT. Anyway, thanks. – GianMS May 13 '17 at 13:50
  • Yeah if you have a UDF or you are using ADO and you make use of an output parameter or the `Return_Value` parameter. http://stackoverflow.com/a/706363/491907 – pinkfloydx33 May 13 '17 at 15:51
  • Do you see the correct parameters being sent to SQL server if you view the SP call in SQL profiler ? – OrdinaryOrange Jul 08 '17 at 08:17

0 Answers0