Help Needed, Stored Procedure and Return Value 1

[SuperCyber]

I cant seem to get this code to work. Input is just a login user name (@Login) and password (@Password). It returns the proper values for the user information but I cant get the OUTPUT variable (@RETVAL) to return the correct value. Any help would be greatly appreciated, Ive worked on this for a day now.

Thanks,
Brian

Alter Procedure sproc_CustLogin
@Login varchar(50),
@Password varchar(50),
@RETVAL int = null OUTPUT
AS
SELECT custID, custFirstName, custLastName
FROM tblCustomers
WHERE @Login = custEmail
AND @Password = custPassword
ORDER BY custID

IF @Login = 'custEmail' AND @Password = 'custPassword'
BEGIN
SELECT @RETVAL = 3
RETURN @RETVAL
END

ELSE IF @Login = 'custEmail' AND @Password != 'custPassword'
BEGIN
SELECT @RETVAL = 2
RETURN @RETVAL
END

ELSE IF @Login != 'custEmail' AND @Password = 'custPassword'
BEGIN
SELECT @RETVAL = 1
RETURN @RETVAL
END

ELSE IF @Login != 'custEmail' AND @Password != 'custPassword'
BEGIN
SELECT @RETVAL = 0
RETURN @RETVAL
END

 

[jhall156]

None of your if's will ever fire and here's why:
You're comparing your input parameters to literal strings and we can safely assume that none of your logins or passwords will match those literals.

Also,
If you want to derive information about matches on both of these criteria you will need to adjust your approach. You can't compare the tuples of a result in an if statement once the query is complete. You'll need to either place multirow results in a cursor and fetch those rows into variables for comparison or select your results directly into variables if you're certain you're returning only one row.

 

[vishnuprasad]

Your query comparision is wrong.

U have to specify "Columnname=value" condition in the where clause while u r specifying "value=value" clause
SELECT custID, custFirstName, custLastName
FROM tblCustomers

<here is what the problem lies>
WHERE @Login = custEmail
AND @Password = custPassword
ORDER BY custID

<change this to>
WHERE custEmail=@Login
AND custPassword=@Password
ORDER BY custID

This would work.

 

[jhall156]

This part is OK. SQL Server doesn't concern itself with left or right values when using = as a comparison operator vice assignment operator.

SELECT custID, custFirstName, custLastName
FROM tblCustomers
WHERE @Login = custEmail
AND @Password = custPassword
ORDER BY custID

is the same as

SELECT custID, custFirstName, custLastName
FROM tblCustomers
WHERE custEmail = @Login
AND custPassword = @Password
ORDER BY custID

 

[SuperCyber]

That does make sense, but I'm unsure on exactly how to fix the problem. I've tried to use CASE Statements and the ELSE IF Statements inside and outsied the SELECT Statement with no luck. Sorry, I'm am a little new to SQL and trying to pick it up as fast as I can.

Thanks,
Brian

 

[jhall156]

What is the requirement. Do you really want to tell if the password is legal but doesn't match the login supplied? I can see if you want to say 'Hey the password is wrong' implicitly telling the user that the login is valid but doesn't match the password but the rest...
Let me know and I'll be glad to show you some simple ways of doing this sort of schtuff.

 

[SuperCyber]

Thank you, I appreciate this very much. Basically, to login to the website the user enters their email address and password which is validated from the SQL database. Upon login I want to pass their email address (@Login) and password (@Password) to the stored prcedure. That all seems to work. What I am now trying to accomplish is:

1 - If the login value exists and the password for the login value is correct then return the users ID, First Name, Last Name, and a return value of &quot;3&quot; back to the web page. Form there it will be redirected to the authorized user area.

OR

2 - If the Login value is found but the password value for that Login value is incorrect, then return value of &quot;2&quot; back to the web page which will redirect the user to a &quot;Login Failed&quot; page.

OR

3 - If the email is not found, then return value of &quot;1&quot; back to the web page which will redirect the user to an &quot;Unauthorized User&quot; page.

OR

4 - Well, my mistake, I guess there is no need to have this one if the email in the previous argument doesn’t come back as correct.


Thanks again,
Brian

 

[jhall156]

Try this.

Alter Procedure sproc_CustLogin
@Login varchar(50),
@Password varchar(50),
@RETVAL int = null OUTPUT
AS


DECLARE @custEmail varchar(50), @custPassword varchar(50)

/* Pull the record regardless of password */
SELECT @custEmail = custEmail, @custPassword = custPassword
FROM tblCustomers
WHERE custEmail = @Login

IF @@ROWCOUNT > 0 /*Found the login*/
BEGIN
SELECT @RETVAL =
CASE
WHEN @Password = @custPassword THEN 3
ELSE 2
END
END
ELSE /*No Gots*/
SELECT @RETVAL = 1

RETURN @RETVAL

 

[jhall156]

By the by. Are you running your IIS in NT 4.0 or Windows 2000? It would probably be easier to integrate windows security.

 

[SuperCyber]

WOW, Your code worked great! I have a much more clear understanding of what you meant, I see now that I was looking at it more one dimensionally. I was trying to ask for values and use declared values in the same SELECT STATEMENT, I added the SELECT STATEMENT for the customer return values after the CASE STATEMENT and inside an IF STATEMENT. And it now works perfect! Thank you!

Brian

Alter Procedure sproc_CustLogin
@Login varchar(50),
@Password varchar(50),
@RETVAL int = null OUTPUT
AS
DECLARE @custEmail varchar(50), @custPassword varchar(50)

/* Pull the record regardless of password */
SELECT @custEmail = custEmail, @custPassword = custPassword
FROM tblCustomers
WHERE custEmail = @Login

IF @@ROWCOUNT > 0 /*Found the login*/
BEGIN
SELECT @RETVAL =
CASE
WHEN @Password = @custPassword THEN 3
ELSE 2
END
IF @RETVAL = 3
BEGIN
SELECT custID, custFirstName, custLastName
FROM tblCustomers
WHERE custEmail = @Login
END
END
ELSE /*No Gots*/
SELECT @RETVAL = 1

RETURN @RETVAL

 

[SuperCyber]

Also, I'm running IIS on W2K. I am in the development stage of an Online ordering system and I was told we would be migrating to new servers at least twice (out of my control, politics I think). So instead of messing with user accounts in the OS I made the SQL hold all of the user information thinking that it would help minimize issues.

Brian

 

[jhall156]

I understand my brother/sister, but be aware that this method is not very secure.

 

[SuperCyber]

There isn't anything secure on the site; all ordering info has to be faxed along with other needed information to the ordering center due to Customs (Canadian company). The site is mainly for a user to login and select what they would like to order, the site then totals everything up, asks a few dropdown box questions, and then the user enters their method of payment but we don't store the method of payment information. After a bunch of java scripting happens they can then print out their order so it can be faxed.

Thanks,
Brian