stored procedure RSS

• • Reply
  

  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

  Please click "Mark as Answer" if this helped you.
  My Blog: http://shivaniaspnet.blogspot.in
• • Reply
  

  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 ;

• • Reply
  

  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.

  Please click "Mark as Answer" if this helped you.
  My Blog: http://shivaniaspnet.blogspot.in
  • Marked as answer by shivani.gupta on Aug 03, 2012 08:36 AM