SQL Stored Procedures Log In Problem?

Question

I want to create a stored procedure that check whether the username and the password is exists and match respectively.

But when I execute the stored procedure with either a wrong username or a wrong password, it would still return a valid return (0).

I'm not very good in SQL.. So please help me out.. Thanks..
__________________________________

Create Proc spLoginUser
(@LoginUserId VarChar(10), @LoginUserPwd VarChar(20)= Null)
As
Begin
If Exists (Select LoginUserID From LoginUser)
Begin
SELECT LoginUserNo, LoginUserType
From LoginUser
Where LoginUserID = @LoginUserID and LoginUserPwd = @LoginUserPwd
IF @@Rowcount < 1
SELECT @LoginUserID = 0
End
Else
Return -102
If @@ERROR <> 0
Return -103
Return

Declare @NewId Int, @Status Int
Exec @Status = spLoginUser @LoginUserId='cusat01', @LoginUserPwd='pass'
Select @Status
If @status = 0
Select @NewID

Answer 1

Other than the heading stuff, you can reduce the whole thing down to just the if statement. Exists will determine if the user is there.





if Exists (SELECT LoginUserNo, LoginUserType

From LoginUser

Where LoginUserID = @LoginUserID and LoginUserPwd = @LoginUserPwd)

begin

return 0

end

else

begin

/* check to see if an error brought us here */

if @@ERROR <> 0

return somecode

else

return another code indicating record not found



end

Answer 2

I think you need a where clause in the IF statement:

If Exists (Select LoginUserID From LoginUser Where LoginUserID = @LoginUserID and LoginUserPwd = @LoginUserPwd)



If you don't have the where clause you get a lot of records back, so the IF statement will be true as long as there is one row.



The next thing I notice is this:

IF @@Rowcount < 1

SELECT @LoginUserID = 0

End



@LoginUserID is not an output parameter, so setting it to zero does nothing.



Finally, I would try to not use multiple return statements. It is bad practice. Declare an integer variable inside of your procedure to hold your return code.



T-SQL is a fun language. Keep it up, it doesn't look bad.

Answer 3

Hmmm.. I will have to look this over more closely when I get some time. I can say that I don't quite understand the lines



IF @@Rowcount < 1

SELECT @LoginUserID = 0

End



If maybe you could explain a little more, I could probably help. I am not sure what the -102 and -103 are for - are you capturing specific codes to detect specific kinds of errors?

Answer 4

If Exists (Select LoginUserID From LoginUser)

Change that line to



If Exists (Select LoginUserId From LoginUser Where LoginUserId = @LoginUserID)



You are looking if the user id exists.



Also if you are going to do this check then there is no need to perform the @@Rowcount anymore.