Having issues updating a column in my db using a SPROC

This stored procedure doesn’t update the column FailedLoginAttempts if the conditions are true. Can anyone tell me what might be wrong with this?

ALTER PROCEDURE UpdateFailedLoginAttempts
        @username varchar(100),
        @failureType varchar(100),
        @maxInvalidPasswordAttempts int

    DECLARE @failureCount int

    IF (@failureType='Password') BEGIN
        SELECT @failureCount=FailedLoginAttempts FROM Users
        WHERE Username=@username

        UPDATE Users SET FailedLoginAttempts=@failureCount+1 WHERE Username=@username    

        IF(@failureCount >= @maxInvalidPasswordAttempts) BEGIN
            UPDATE Users SET IsUserLocked = 1 WHERE Username=@username

What i’m trying to achieve is if the failureType is Password, then it should SET @failureCount equal to FailedLoginAttempts and then update FailedLoginAttempts to @failureCount + 1 (to increase the amount of failed attempts) and then check if @failureCount is greater than or equal to @maxInvalidPasswordAttempts.

  • SQL server schema and default schema
  • Bespoke SQL Server 'encoding' sproc - is there a neater way of doing this?
  • How to debug stored procedures with print statements?
  • SQL Server - CTE with 2 tables until qty consumed
  • How to Delete all data from a table which contain self referencing foreign key
  • can we copy data from on column to another with different datatypes?
  • 2 Solutions collect form web for “Having issues updating a column in my db using a SPROC”

    One thing I am noticing is that you:

    • set @failureCount = FailedLoginAttempts

    • update FailedLoginAttempts = @failureCount + 1

    • check @failureCount >= @maxInvalidPasswordAttempts

    This is not checking against the updated FailedLoginAttempts value, so you will likely have to fail one additional time than you expect before an account is locked.

    Since you have to run an update anyway, you could consider this route:

    UPDATE u
    SET u.IsUserLocked =
          WHEN (FailedLoginAttempts + 1) >= @maxInvalidPasswordAttempts THEN 1
          ELSE u.IsUserLocked
       , u.FailedLoginAttempts = u.FailedLoginAttempts + 1
    FROM Users AS u
    WHERE u.Username = @username

    It removes the need to make multiple statements, or the variable @failureCount

    Can you check if your column FailedLoginAttempts has a default value of 0 and not null?

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.