Noob - determine what part of SELECT is not matching? 2

[jacktripper]

I am a completely average query programmer. I built a simple SPROC so a user can retrieve their password from the system based on 3 criteria. The SELECT query either finds a match for all 3 in the WHERE clause, or it fails.

The 3 criteria are e-mail, birthdate, and zipcode.

But, I'd like to be able to tell the user what part of the SELECT failed. For example, "The e-mail could not be found in the system". Or, if the e-mail IS found, but the zipcode doesn't match... then be able to say that to them.

I started to build a sproc to do this, but its not going well. I figure there must be an easier way??? Either that or my If/Else is just stupid, because I get a blank result from the ELSE section if I have a matching E-mail.

What I started with is:
SELECT @FoundEmail = Employee.Email
FROM Employee
WHERE (Employee.Email=@Email)

If @FoundEmail = ''
Set @ReturnMessage = 'That E-mail was not found in our system'

Else
SELECT @FoundZip = Employee.Zipcode
FROM Employee WHERE Employee.Email = @Email

If @FoundZip = '' and @FoundEmail <> ''
Set @ReturnMessage = 'e-mail found but cannot find zipcode'

END

Any thoughts?

 

[bborissov]

DECLARE @ReturnMessage varchar(200)

IF NOT EXISTS(SELECT *
                    FROM Employee
              WHERE (Email = @Email))
   SET @ReturnMessage = 'That E-mail was not found in our system'    
ELSE IF NOT EXISTS(SELECT *
                      FROM Employee
                   WHERE Email   = @Email AND
                         ZipCode = @ZipCode))
   SET @ReturnMessage = 'E-mail found but cannot find zipcode'

ELSE IF NOT EXISTS(SELECT *
                          FROM Employee
                   WHERE Email   = @Email   AND
                         ZipCode = @ZipCode AND
                         BirthDay = @BirthDay))
   SET @ReturnMessage = 'Everything else is found but birthday'
ELSE 
  BEGIN
      SET @ReturnMessage = ''
      SELECT *
             FROM Employee
      WHERE Email   = @Email   AND
            ZipCode = @ZipCode AND
            BirthDay = @BirthDay
  END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jacktripper]

Wow... Thanks! I will try it today.

 

[bborissov]

Watch out for typos )))
I just saw that I have two closing brackets there where I need just one

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SQLSister]

YOu aren;t storing passwords unencrypted are you? The correct process when a user forgets a password is to send them a new one not to retrieve the old one which you should not be able to directly read in the datbase.

"NOTHING is more important in a database than integrity." ESquared

 

[MarkXaml]

Also, informing the user what parts of their "guess" are incorrect only assist hacking attempts. Especially considering that the 3 criteria that you are requiring are probably on their Facebook page. The attempt should either fail or succeed without hints to what was entered incorrectly, imo.

Using SQLSister's advice would at least only let people get in that have access to the user's email.

Just my 2 cents. Good luck!

-Mark

 

[jacktripper]

Unfortunately, I was not the creator of this website, and I have made several arguments that the security stinks.

And yes, I am e-mailing them a new password, so they at least need to have access to the e-mail they use.

It is on the agenda for more improvements in this area.

Thanks!