locked
MySQL Syntax RRS feed

  • Question

  • User1182241144 posted

    I am facing some problem while creating stored procedure in MYSQL

    Can anybody tell me the what is syntax error in below procedure

    BEGIN
    DECLARE @TotalRows INT DEFAULT 0;
    SELECT COUNT(*) INTO @TotalRows FROM PasswordHistory WHERE UserId=UId;
    IF (@TotalRows=PassLimit)THEN
     DELETE FROM PasswordHistory Where UserId=UId and CreatedDate=(Select min(createdDate) from PasswordHistory where UserId=UId);
    END IF;
     INSERT INTO PasswordHistory VALUES(UId, UPass, NOW());
     UPDATE UserLogin SET Password=UPass WHERE UserId=Uid;
    END

    Friday, July 20, 2012 7:41 AM

Answers

  • User1182241144 posted

    I did this way at it solved  my problem:

    BEGIN
    DECLARE TotalRows INT DEFAULT 0;
    SELECT COUNT(*) INTO @TotalRows FROM PasswordHistory WHERE UserId=UId;
    IF (@TotalRows=PassLimit) THEN
    SELECT MIN(createdDate) INTO @MinimumDate FROM PasswordHistory WHERE UserId=UId;
    DELETE FROM PasswordHistory WHERE UserId=UId and CreatedDate=@MinimumDate;
    END IF;
    INSERT INTO PasswordHistory VALUES(UId, UPass, NOW());
    UPDATE UserLogin SET Password=UPass WHERE UserId=Uid;
    SET PassStatus=1;
    END

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 23, 2012 8:22 AM

All replies

  • User-802546231 posted

    use set @TotalRows = 0; instead of DECLARE @TotalRows INT DEFAULT 0; to declare your variable

    Friday, July 20, 2012 8:08 AM
  • User1182241144 posted

    Solved my problem by doing this:

    I removed @ from this line "DECLARE TotalRows INT DEFAULT 0; "

    BEGIN
     DECLARE TotalRows INT DEFAULT 0;
     SELECT COUNT(*) INTO @TotalRows FROM PasswordHistory WHERE UserId=UId;
     IF (@TotalRows=PassLimit) THEN
      DELETE FROM PasswordHistory WHERE UserId=UId and CreatedDate=(SELECT MIN(createdDate) FROM PasswordHistory WHERE UserId=UId);
     END IF;
      INSERT INTO PasswordHistory VALUES(UId, UPass, NOW());
      UPDATE UserLogin SET Password=UPass WHERE UserId=Uid;
    END

    Friday, July 20, 2012 8:26 AM
  • User1182241144 posted

    After solving my above problem

    while executing this procedure its saying "You can't specify target table 'PasswordHistory' for update in FROM clause"

    Please help

    Friday, July 20, 2012 9:13 AM
  • User1784393945 posted

    "You can't specify target table 'PasswordHistory' for update in FROM clause"

    Shivani,

    In MySQL, you can't modify the same table which you use in the SELECT part.
    This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html

    You will need to stop using the nested subquery and execute the operation in two parts, or alternatively use a simple where clause.

    Let me know any other issues you face with this..

    Monday, July 23, 2012 1:33 AM
  • User1182241144 posted

    I did this way at it solved  my problem:

    BEGIN
    DECLARE TotalRows INT DEFAULT 0;
    SELECT COUNT(*) INTO @TotalRows FROM PasswordHistory WHERE UserId=UId;
    IF (@TotalRows=PassLimit) THEN
    SELECT MIN(createdDate) INTO @MinimumDate FROM PasswordHistory WHERE UserId=UId;
    DELETE FROM PasswordHistory WHERE UserId=UId and CreatedDate=@MinimumDate;
    END IF;
    INSERT INTO PasswordHistory VALUES(UId, UPass, NOW());
    UPDATE UserLogin SET Password=UPass WHERE UserId=Uid;
    SET PassStatus=1;
    END

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 23, 2012 8:22 AM