CREATE PROCEDURE validateLogin
    @username varchar(30),
    @password varchar(30),
    @rank varchar(50) OUTPUT,
    @error varchar(100) OUTPUT
AS
    IF EXISTS(SELECT * from users1 where username=@username)
        IF EXISTS(SELECT * from users1 where username=@username AND password=@password)
            BEGIN
                --valid login, return the rank
--                 SET @rank = (select rank from users1 where username=@username AND password=@password)
                SELECT @rank = u.rank from users1 u where u.username=@username AND u.password=@password
            END
        ELSE
            BEGIN
                SET @error = 'Invalid Password'
            END
    ELSE
        BEGIN
            SET @error = 'No Such User'
        END
    RETURN
GO