please see my bold lines it always return 1 in my UId into variable
Can anyone tell the reason
BEGIN
DECLARE UId INT DEFAULT 0;
DECLARE AtNo INT DEFAULT 0;
DECLARE MaxAttempt INT DEFAULT 0;
DECLARE DBPass VARCHAR(255); SELECT UserId INTO UId FROM UserLogin WHERE UserName=username AND IsLock=0 ;
INSERT INTO test values(1,UId);
IF(UId>0) THEN
SELECT Password INTO DBPass FROM UserLogin WHERE Password=pass and UserId=UId LIMIT 1;
INSERT INTO test values(2,DBPass);
IF(DBPass IS NULL) THEN
DELETE FROM LoginAttempt WHERE UserId=UId AND AttemptDate<CURRENT_DATE() ;
SELECT AttemptNo INTO AtNo FROM LoginAttempt WHERE UserId=UId AND AttemptDate=CURRENT_DATE() ;
IF(AtNo=0) THEN
INSERT INTO LoginAttempt VALUES(UId, 1, CURRENT_DATE());
SET userstatus=-1; #Invalid Login Attempt
ELSE
SELECT ConfigKeyValue INTO MaxAttempt FROM ConfigTable WHERE ConfigKeyName='MaxAttemptofPassword';
IF(AtNo<MaxAttempt) THEN
UPDATE LoginAttempt SET AttemptNo=AtNo+1 WHERE UserId=UId;
SET userstatus=-1; #Invalid Login Attempt
ELSE
UPDATE UserLogin SET IsLock=2 WHERE UserId=UId;
SET userstatus=-2; #Password Blocked
END IF;
END IF;
ELSE
SELECT UId, DATEDIFF(CURRENT_DATE(),PasswordUpdated) AS PasswordUpdatedDays, PasswordUpdated, LastLogin FROM UserLogin WHERE UserId=UId;
DELETE FROM LoginAttempt WHERE UserId=UId;
SET userstatus=1; #User found
END IF;
ELSE
SET userstatus=-3; #Invalid User
END IF;
END
Please click "Mark as Answer" if this helped you.
My Blog: http://shivaniaspnet.blogspot.in
shivani.gupt...
Participant
857 Points
312 Posts
stored procedure
Aug 03, 2012 06:57 AM|LINK
Hi friends
please see my bold lines it always return 1 in my UId into variable
Can anyone tell the reason
BEGIN
DECLARE UId INT DEFAULT 0;
DECLARE AtNo INT DEFAULT 0;
DECLARE MaxAttempt INT DEFAULT 0;
DECLARE DBPass VARCHAR(255);
SELECT UserId INTO UId FROM UserLogin WHERE UserName=username AND IsLock=0 ;
INSERT INTO test values(1,UId);
IF(UId>0) THEN
SELECT Password INTO DBPass FROM UserLogin WHERE Password=pass and UserId=UId LIMIT 1;
INSERT INTO test values(2,DBPass);
IF(DBPass IS NULL) THEN
DELETE FROM LoginAttempt WHERE UserId=UId AND AttemptDate<CURRENT_DATE() ;
SELECT AttemptNo INTO AtNo FROM LoginAttempt WHERE UserId=UId AND AttemptDate=CURRENT_DATE() ;
IF(AtNo=0) THEN
INSERT INTO LoginAttempt VALUES(UId, 1, CURRENT_DATE());
SET userstatus=-1; #Invalid Login Attempt
ELSE
SELECT ConfigKeyValue INTO MaxAttempt FROM ConfigTable WHERE ConfigKeyName='MaxAttemptofPassword';
IF(AtNo<MaxAttempt) THEN
UPDATE LoginAttempt SET AttemptNo=AtNo+1 WHERE UserId=UId;
SET userstatus=-1; #Invalid Login Attempt
ELSE
UPDATE UserLogin SET IsLock=2 WHERE UserId=UId;
SET userstatus=-2; #Password Blocked
END IF;
END IF;
ELSE
SELECT UId, DATEDIFF(CURRENT_DATE(),PasswordUpdated) AS PasswordUpdatedDays, PasswordUpdated, LastLogin FROM UserLogin WHERE UserId=UId;
DELETE FROM LoginAttempt WHERE UserId=UId;
SET userstatus=1; #User found
END IF;
ELSE
SET userstatus=-3; #Invalid User
END IF;
END
My Blog: http://shivaniaspnet.blogspot.in
Anish V.S.
Member
522 Points
123 Posts
Re: stored procedure
Aug 03, 2012 07:03 AM|LINK
Replace the query with
SELECT UId=UserId FROM UserLogin WHERE UserName=username AND IsLock=0 ;
shivani.gupt...
Participant
857 Points
312 Posts
Re: stored procedure
Aug 03, 2012 08:36 AM|LINK
Thanks for reply... My problem has been resolved now.
In MYSQL stored procedure you cannot take variable name and column name same. it will create a problem.
My Blog: http://shivaniaspnet.blogspot.in