[SOLVED] write a procedure to get multiple records

Issue

I am writing a procedure, I want to get multiple records, Below is my query, any help please

CREATE PROCEDURE `callHolidays`()
BEGIN
SET @emp = (SELECT id,name from default_holidays WHERE year = '2022');
SET @empCount = (SELECT count(id) as count from default_holidays WHERE year = '2022');
IF @empCount > 0 THEN
 WHILE condition

  INSERT INTO 
  holidays(id,company_id,name,date,all_years,year)values(values);

 END WHILE
END

Solution

I suspect you may not be aware of INSERT..SELECT https://dev.mysql.com/doc/refman/8.0/en/insert-select.html

Your entire code could be replaced by

INSERT INTO  holidays
 (id,
 #company_id,
 name,
 #date, all_years,
 year)
 select id,name,year
 from default_holidays
 where year = '2022';

And I would paramterise the year test so you don’t have to change it every year.

Answered By – P.Salmon

Answer Checked By – Senaida (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.