How to set up a WHILE loop with IF statement in MySQL?

Question

I would like to create a stored routine for MySQL that figures out the number of business or working days for a month (Working Days are Monday thru Friday).

It's a syntax error however I don't know what the syntax error is. All it tells me is:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE(@daycount < @totaldays) DO IF (WEEKDAY(@checkweekday) < 6) THEN ' at line 2

My Syntax Error is in the following:

WHILE(@daycount < @totaldays) DO
      IF (WEEKDAY(@checkweekday) < 6) THEN

My Code:

SELECT MONTH(CURDATE()) INTO @curmonth;
SELECT MONTHNAME(CURDATE()) INTO @curmonthname;
SELECT DAY(LAST_DAY(CURDATE())) INTO @totaldays;
SELECT FIRST_DAY(CURDATE()) INTO @checkweekday;
SELECT DAY(@checkweekday) INTO @checkday;
SET @daycount = 0;
SET @workdays = 0;

BEGIN
  WHILE(@daycount < @totaldays) DO
      IF (WEEKDAY(@checkweekday) < 6) THEN
        SET @workdays = @workdays+1;
      END IF;
      SET @daycount = @daycount+1;
      SELECT ADDDATE('@checkweekday', INTERVAL 1 DAY) INTO @checkweekday;
  END WHILE;
END;
SELECT @workdays;

Is someone able to assist?

UPDATE I receive the same error with the following bit of code so it probably has something to do with this:

SET @workdays = 0;
IF (WEEKDAY('2013-06-13') < 6) THEN
  SET @workdays = @workdays+1;
END IF;
SELECT @workdays;

Yes I do. The webhost actually has that function automatically. I wrote my own and realized that when I use the function without mine it still works. The first_day() is not where the error is anyway. It passes it without a problem. — scrfix, Jun 17 '13 at 02:47
for those who end up here, I made a concise While-Loop example [here](https://gist.github.com/Foadsf/7dcefc25d70a8f9147d10ce2ed881721) — Foad S. Farimani, Mar 27 '20 at 12:11

score 26 · Accepted Answer · edited Oct 19 '21 at 01:29

I have discovered that you cannot have conditionals outside of the stored procedure in mysql. This is why the syntax error. As soon as I put the code that I needed between

BEGIN
SELECT MONTH(CURDATE()) INTO @curmonth;
SELECT MONTHNAME(CURDATE()) INTO @curmonthname;
SELECT DAY(LAST_DAY(CURDATE())) INTO @totaldays;
SELECT FIRST_DAY(CURDATE()) INTO @checkweekday;
SELECT DAY(@checkweekday) INTO @checkday;
SET @daycount = 0;
SET @workdays = 0;

  WHILE(@daycount < @totaldays) DO
    IF (WEEKDAY(@checkweekday) < 5) THEN
      SET @workdays = @workdays+1;
    END IF;
    SET @daycount = @daycount+1;
    SELECT ADDDATE(@checkweekday, INTERVAL 1 DAY) INTO @checkweekday;
  END WHILE;
END

Just for others:

If you are not sure how to create a routine in phpmyadmin you can put this in the SQL query

delimiter ;;
drop procedure if exists test2;;
create procedure test2()
begin
select ‘Hello World’;
end
;;

Run the query. This will create a stored procedure or stored routine named test2. Now go to the routines tab and edit the stored procedure to be what you want. I also suggest reading http://net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/ if you are beginning with stored procedures.

The first_day function you need is: How to get first day of every corresponding month in mysql?

Showing the Procedure is working Simply add the following line below END WHILE and above END

SELECT @curmonth,@curmonthname,@totaldays,@daycount,@workdays,@checkweekday,@checkday;

Then use the following code in the SQL Query Window.

call test2 /* or whatever you changed the name of the stored procedure to */

NOTE: If you use this please keep in mind that this code does not take in to account nationally observed holidays (or any holidays for that matter).

How to set up a WHILE loop with IF statement in MySQL?

1 Answers1

Linked