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 ) AS BEGIN 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 END END END
What i’m trying to achieve is if the
failureType is Password, then it should SET
@failureCount equal to
FailedLoginAttempts and then update
@failureCount + 1 (to increase the amount of failed attempts) and then check if
@failureCount is greater than or equal to
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:
@failureCount = FailedLoginAttempts
FailedLoginAttempts = @failureCount + 1
@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 = CASE WHEN (FailedLoginAttempts + 1) >= @maxInvalidPasswordAttempts THEN 1 ELSE u.IsUserLocked END , u.FailedLoginAttempts = u.FailedLoginAttempts + 1 FROM Users AS u WHERE u.Username = @username
It removes the need to make multiple statements, or the variable
Can you check if your column
FailedLoginAttempts has a default value of 0 and not null?