I am trying to create a stored procedure to validate a user(login), everything is working except for when the user enters an email which is NOT registered. You see I have handled that exception wherein if the count of email address in the table is 0,it should give out:
Select 0 as AccountLocked,0 as Authenticated,0 as RetryAttempts,0 as Registered
and if it is not 0, it will execute the next steps.The problem is when the enter an email which is not registered, it gives me back two sets of results which are as below:
Select 0 as AccountLocked,0 as Authenticated,0 as RetryAttempts,0 as Registered
and
Select 1 as AccountLocked,0 as Authenticated,0 as RetryAttempts,1 as Registered
The complete proc is given for reference.What am i missing here? why is it giving me the second result too which I don't want?
Alter proc spValidateUser
@EmailAdd nvarchar(30),
@Password nvarchar(20)
as
begin
Set Nocount on;
Declare @UserId nvarchar(10),@LastLogin datetime,@RoleId int,@AccountLocked bit,@RetryCount int,@Count int
Select @Count=Count(EmailAdd) from tblAllUsers
where EmailAdd=@EmailAdd
if(@Count = 0)
begin
Select 0 as AccountLocked,0 as Authenticated,0 as RetryAttempts,0 as Registered
end
else
Select @AccountLocked=IsLocked from tblAllUsers where EmailAdd=@EmailAdd
----if account is already locked------
if(@AccountLocked = 1)
begin
Select 1 as AccountLocked,0 as Authenticated,0 as RetryAttempts,1 as Registered
end
else
begin
-----check if username and password match-----
Select @UserId = UserId, @LastLogin=LastLogin, @RoleId=RoleId
from tblAllUsers where EmailAdd=@EmailAdd and Password=@Password
----if match found--------
If @UserId is not null
Begin
Update tblAllUsers
SET LastLogin= GETDATE(),RetryAttempts=0 WHERE UserId=@UserId
Select @UserId [UserId],
(Select Role from tblRoles where RoleId=@RoleId) [Roles],0 as AccountLocked,1 as Authenticated,0 as RetryAttempts,1 as Registered
End
Else
------if match not found--------
Begin
Select @RetryCount=ISNULL(RetryAttempts,0) from tblAllUsers where EmailAdd=@EmailAdd
Set @RetryCount=@RetryCount+1
if(@RetryCount<=3)
Begin
----if retry attempts are not completed------
Update tblAllUsers Set RetryAttempts=@RetryCount where EmailAdd=@EmailAdd
Select 0 as AccountLocked,0 as Authenticated,@RetryCount as RetryAttempts,1 as Registered
End
Else
Begin
------if retry attempts are completed--------
Update tblAllUsers Set RetryAttempts=@RetryCount,IsLocked=1,LockedDateTime=GETDATE()
where EmailAdd=@EmailAdd
Select 1 as AccountLocked,0 as Authenticated,0 as RetryAttempts,1 as Registered
End
End
End
End
Edit: Looks like it is executing the below code too :
Begin
------if retry attempts are completed--------
Update tblAllUsers Set RetryAttempts=@RetryCount,IsLocked=1,LockedDateTime=GETDATE()
where EmailAdd=@EmailAdd
Select 1 as AccountLocked,0 as Authenticated,0 as RetryAttempts,1 as Registered
End
but why should it execute the above when the emails don't match?
Aucun commentaire:
Enregistrer un commentaire