MySQL Stored Routine Error

Question

I'm creating Stored Routine in MySQL right now, and I got this error while querying.

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your SQL server version for the right syntax to use near 'DATE_ADD(sDate, INTERVAL 1 DAY); END WHILE; END IF; END' at line 30

And here is my Query:

BEGIN
DECLARE code CHAR (5) DEFAULT (00000);
IF (SELECT COUNT(allocationDate) FROM ROOM_ALLOCATION WHERE allocationDate 
 BETWEEN startDate AND (SELECT DATE_SUB(endDate,INTERVAL 1 DAY)) > 0) THEN    
    WHILE DATEDIFF(sDate, eDate) <> 0 DO
        SET code = 0;
        WHILE code = 0 DO
            SET code =(SELECT LEFT(MD5(RAND()*100), 5));
            IF exists (SELECT * FROM BOOKING_DETAIL WHERE bDetailCode LIKE code)
            THEN 
                SET code = 0;
        END IF;
        END WHILE;
        INSERT INTO ROOM_ALLOCATION (rAllocationCode, roomID, bDetailID, allocationDate)
        VALUES
        (rAlloCode, roIDm, bDetID, sDate);
        DATE_ADD(sDate, INTERVAL 1 DAY);
    END WHILE;
END IF;
END

I am desperate where did I go wrong with this query?

Thank you beforehand.

Regards

You aren't doing anything with this statement `DATE_ADD(sDate, INTERVAL 1 DAY);` What do you want to do with it? Select or Set a variable? — SS_DBA, Feb 27 '18 at 14:17

score 1 · Accepted Answer · answered Feb 27 '18 at 14:58

sDate needs to be declared as a variable, and I assume you're passing startDate in to the procedure.

You set sDate equal to the startDate which now allows you to manipulate sDate using DATEADD. DATEADD returns the value, it doesn't modify the variable; so you use SET and assign the return value of DATEADD back to sDate so your WHILE loop uses it in the next loop.

Because eDate doesn't exist, I've modified the instances of eDate to use the endDate I assume you're passing in too. Because you don't modify that value, it doesn't make sense to reassign it to another variable.

BEGIN
DECLARE code CHAR (5) DEFAULT (00000);
DECLARE sDate DATETIME = startDate;
IF (SELECT COUNT(allocationDate) FROM ROOM_ALLOCATION WHERE allocationDate 
 BETWEEN startDate AND (SELECT DATE_SUB(endDate,INTERVAL 1 DAY)) > 0) THEN    
    WHILE DATEDIFF(sDate, endDate) <> 0 DO
        SET code = 0;
        WHILE code = 0 DO
            SET code =(SELECT LEFT(MD5(RAND()*100), 5));
            IF exists (SELECT * FROM BOOKING_DETAIL WHERE bDetailCode LIKE code)
            THEN 
                SET code = 0;
        END IF;
        END WHILE;
        INSERT INTO ROOM_ALLOCATION (rAllocationCode, roomID, bDetailID, allocationDate)
        VALUES
        (rAlloCode, roIDm, bDetID, sDate);
        SET sDate = DATE_ADD(sDate, INTERVAL 1 DAY);
    END WHILE;
END IF;
END

Small issue, in an otherwise excellent answer. To set sDate to the input parameter in the variable declaration use DECLARE sDate DATETIME DEFAULT(startDate); Assignment with '=' will give a syntax error. — Paul Campbell, Apr 19 '18 at 08:31

MySQL Stored Routine Error

1 Answers1