Stored Procedure that it's not working

Question

I have this procedure, but it doesn't work..I can't figure it out why. Any ideas what am I doing wrong?

CREATE PROCEDURE [dbo].[spCheckLogin]  
  (  @Username varchar(30),
     @Password varchar(15))  
AS  
BEGIN  
  Declare 
     @Result int = 0;

IF (@Password=(SELECT password from users where Username=@Username)) 

   set @Result=1 

   ELSE  

   set   @Result=0

   return @Result
END  
GO

Define: "It doesn't work". It generates an error? Doesn't do what you expect? Causes you SQL server to unexpected Shutdown? Cures world Hunger? We can't see what you see. Also, I ***really*** hope you're not storing unhashed (with a salt) passwords in your database. — Thom A, Aug 02 '18 at 14:24
Yes. Don't use `return` when using stored procedures. but more important, never store passwords as plain text in your database. Instead, [store a salted hash of the password.](https://crackstation.net/hashing-security.htm) — Zohar Peled, Aug 02 '18 at 14:25
When I call this procedure from an api it says that the variable I use it doesn't have value. Thing is that I've assigned value 0 when I declared it :) — Mircea, Aug 02 '18 at 14:26
Also a good idea to start your procedure with `set nocount on` — GuidoG, Aug 02 '18 at 14:33
If all you return is just one variable I would consider to create a function in stead of a procedure — GuidoG, Aug 02 '18 at 14:36

score 1 · Answer 1 · answered Aug 02 '18 at 14:24

The comparison you are using is incorrect. It should be something like:

IF EXISTS (SELECT 1 from users where Username=@Username AND password = @password)

Also note that storing passwords in plaintext is not a good practice. Suggested reading: Why are plain text passwords bad, and how do I convince my boss that his treasured websites are in jeopardy?

score 0 · Answer 2 · answered Aug 02 '18 at 14:30

Instead of a return value you would want to use an output value. However a return value is of type integer, thus might work for you.

You would never want to use a plain password stored, but I would assume you are doing this just for testing purposes on a hobby project.

CREATE PROCEDURE [dbo].[spCheckLogin]  
  (  @Username varchar(30),
     @Password varchar(15))  
AS  
BEGIN  
  Declare @Result int = case 
        when exists (SELECT * 
          from users 
          where Username=@Username and password = @Password)) 
        THEN 1 
        ELSE 0
   END
   return @Result
END  
GO

score 0 · Answer 3 · answered Aug 02 '18 at 14:34

Like metnioned, use an OUTPUT paramter:

CREATE PROC [dbo].[spCheckLogin] @Username varchar(30),
                                 @Password varchar(30),
                                 @Success bit OUTPUT
AS
BEGIN

    IF EXISTS (SELECT 1
               FROM users
               WHERE Username = @Username
                 AND Password = @Password)
    BEGIN
        SET @Success = 1;
    END
    ELSE
    BEGIN
        SET @Success = 0;
    END

END

But, also, as mentioned, don't store passwords in an unhashed and unsalted format in your database. If you are, change your design.

Stored Procedure that it's not working

3 Answers3