CODEWITHSUNDEEP
mysqlloopsstored-procedureswhile-loopdo-while
Mohammad Iqbal
Mohammad Iqbal

Reputation: 29

MySQL Stored Routine Error

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

Upvotes: 0

Views: 233

Answers (1)

flip
flip

Reputation: 555

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

Upvotes: 1

Related Questions