Insert values into mysql

Question

I am working on an Attendance project, and this table holds the status of the employee for every month (Permission, Missed, Late etc.). By default, it has be 'Yet to Attend'. I am feeding records into the database for the entire year. Here is my code :

use attendance;
drop procedure setDefault;
DELIMITER $$
create procedure setDefault()
    BEGIN 
        DECLARE theDate date;
        set @theDate = '2020-01-25';

        while  theDate < '2021-01-24'   DO
                INSERT INTO Attendance.empStatus VALUES('4' , theDate , 'YET TO ATTEND');
                set @theDate = @theDate+1;
        end while;
    end $$
 DELIMITER      


call setDefault();

Ps. '4' is the employee id.

Issue is that this is not getting executed. The 'CREATE PROCEDURE...' line is executed, after which it skips to the call line but I don't know why.

Welcome to SmackOverflow. What is your question?

Mm-Art-In
– Mm-Art-In

2020-04-03 16:20:02 +00:00
Commented Apr 3, 2020 at 16:20 — Mm-Art-In
– Mm-Art-In, Commented Apr 3, 2020 at 16:20
I edited it now .

Shanky301
– Shanky301

2020-04-03 16:21:52 +00:00
Commented Apr 3, 2020 at 16:21 — Shanky301
– Shanky301, Commented Apr 3, 2020 at 16:21

GMB · Accepted Answer · 2020-04-03 16:31:43Z

1

Fixes are needed on date arithmetics, stop condition of the loop, and user variables.

This should work:

delimiter $$
create procedure setdefault()
    begin 
        set @thedate := '2020-01-25';
        while @thedate < '2021-01-24'   
        do
            insert into attendance.empStatus values(4 , @thedate , 'yet to attend');
            set @thedate := @thedate + interval 1 day;
        end while;
    end $$  
 delimiter ;

Here is a small demo.

Note that if you are using MySQL 8.0, you can also do this with a recursive query:

insert into empStatus
with recursive cte as (
    select '2020-01-25' dt
    union all select dt + interval 1 day from cte where dt + interval 1 day < '2021-01-24'
)
select 4, dt, 'yet to attend' from cte;

edited Apr 3, 2020 at 16:31

answered Apr 3, 2020 at 16:25

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Insert values into mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related