Hi,
wat i am trying to do is:
when user enter wrong password 3 times his flag changed from 1 to 0 in registration table. -- its done
login SP:
ALTER PROCEDURE [dbo].[USP_UserLogin]
(
@UserName varchar(50),
@Password VARCHAR (80)
)
AS
BEGIN
BEGIN TRY
DECLARE @USERID INT=0
select @USERID=userid from userRegistrationTB where username=@username
IF EXISTS(SELECT userID from userRegistrationTB
WHERE username=@UserName AND password=@Password COLLATE SQL_Latin1_General_CP1_CS_AS AND isActive=1)
BEGIN
SELECT '0|Login successfully' AS result
END
ELSE
BEGIN
SELECT '1|Login Fail' AS result
exec USP_BlockUser @USERID,@password END
END TRY
BEGIN CATCH
SELECT '3|Table not found' As result
END CATCH
END
-----
ALTER PROCEDURE [dbo].[USP_BlockUser]
(
@pswd nvarchar(30),
@userID INT )
AS
BEGIN
-- DECLARE @ID int=0
INSERT INTO userLoginStatusTB
(
wrongAttempt,
isactive,
UserID
)
VALUES
(
0,
0,
@userID
)
Declare @tempAttempt int=0
SELECT @tempAttempt= wrongAttempt from userLoginStatusTB where UserID=@userID
if(@tempAttempt=0)
BEGIN
update userLoginStatusTB set wrongAttempt=1 WHERE UserID=@userID
END
ELSE IF (@tempAttempt=1)
BEGIN
update userLoginStatusTB set wrongAttempt=2 WHERE UserID=@userID
END
ELSE IF(@tempAttempt=2)
BEGIN
update userLoginStatusTB set wrongAttempt=3 WHERE UserID=@userID
END
ELSE
BEGIN
UPDATE userRegistrationTB SET isActive=0 WHERE username=@userID
END
SET NOCOUNT ON;
END
--- i have another table which has user login info. and wrong attempt column when its value become 3 it will update registration table and set flag=0
so user wont be able to login.
wat i want:
after 30 min. this isactive flag will automatically set to 1 and in userLoginStatusTB wrongattempt flat become 0 so that user can again login