dimanche 28 juin 2015

Stored Procedure gives out two sets of unwanted data

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