What's wrong with this SQL Server stored procedure?

Question

I'm trying to write a procedure that will search in my table of members for a matching member with the username and password entered. It should output a 1 if a match is found.

Here is my code:

CREATE PROC LoginProcedure
    @email varchar(50),
    @password varchar(50),
    @found bit output
AS
    IF(EXISTS(SELECT * 
              FROM Members  
              WHERE email = @email AND password = @password))
    BEGIN
        @found = 1; ---HERE
    END
    ELSE
    BEGIN
        @found = 0;  ----HERE
    END
GO  ---HERE

I have commented next to all the lines I'm getting errors at. The error is the same in all 3 lines.

Incorrect Syntax

This Stackoverflow answer may explain things http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference — David Soo, Dec 03 '15 at 19:20
You really should not store passwords in plain text. They should be salted and hashed. — Sean Lange, Dec 03 '15 at 20:30

score 2 · Accepted Answer · answered Dec 03 '15 at 19:16

SQL Server uses set or select to assign values:

CREATE PROCEDURE LoginProcedure (
    @email varchar(50),
    @password varchar(50),
    @found bit output
)
AS BEGIN
    IF (EXISTS (SELECT * FROM Members where email = @email and password = @password  
               ))
    BEGIN
        SET @found = 1; ---HERE
    END;
    ELSE BEGIN
        SELECT @found = 0;  ----HERE
    END;
END; -- LoginProcedure

I think it would be simpler to just write this as a case statement:

BEGIN
   SET @found = (CASE WHEN EXISTS (SELECT * FROM Members where email = @email and password = @password)
                      THEN 1 ELSE 0
                 END);
END;

What's wrong with this SQL Server stored procedure?

1 Answers1