While loop with if statement in MySQL

Question

I am getting a syntax error with this due to the final while loop. I have tried switching it around but cant find the issue here - is it purely that MySQL doesn't support if statements within while loops?

DELIMITER //
CREATE PROCEDURE bLAHbLAHbLAH(p_fromdate datetime, p_todate datetime)
BEGIN

Declare startday datetime;
declare numberofdays int;
declare Counter int;

Set startday = p_fromdate;
Set numberofdays = (select datediff("2012-05-01","2012-11-01"));
Set Counter = 0;

WHILE startday<p_todate DO
    if (select date_format(startday, '%W')) in ('Monday','Tuesday','Wednesday','Thursday','Friday') THEN
        SET Counter = Counter+1;
        SET startday = date_add(startday, INTERVAL 1 DAY);
    else
        SET startday = date_add(startday, INTERVAL 1 DAY);
END WHILE;

select Counter; 

END//

DELIMITER ;

Believe it or not it did. Apologies

user1818768
– user1818768

2012-11-12 19:15:03 +00:00
Commented Nov 12, 2012 at 19:15 — user1818768
– user1818768, Commented Nov 12, 2012 at 19:15

juergen d · Accepted Answer · 2012-11-12 17:09:10Z

5

You forgot an

end if;

before the end while

WHILE startday<p_todate DO
    if (select date_format(startday, '%W')) in ('Monday','Tuesday','Wednesday','Thursday','Friday') THEN
        SET Counter = Counter+1;
        SET startday = date_add(startday, INTERVAL 1 DAY);
    else
        SET startday = date_add(startday, INTERVAL 1 DAY);
    end if;                                                   /*add this line*/
END WHILE;

answered Nov 12, 2012 at 17:09

juergen d

205k40 gold badges303 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1818768 Over a year ago

You are a star, thank you so much. I didn't even realise you had to explicitly end an if statement in MySQL! Doh!!0

Collectives™ on Stack Overflow

While loop with if statement in MySQL

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related