locked
stored procedure RRS feed

  • Question

  • User1182241144 posted

    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

    Friday, August 3, 2012 2:57 AM

Answers

  • User1182241144 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 3, 2012 4:36 AM

All replies

  • User112053641 posted

    Replace the query with

    SELECT UId=UserId FROM UserLogin WHERE UserName=username AND IsLock=0 ;

    Friday, August 3, 2012 3:03 AM
  • User1182241144 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 3, 2012 4:36 AM