a simple schema to demonstrate stored procedures in mysql

absence-schema.sql

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

ALTER SCHEMA `calendar`  DEFAULT CHARACTER SET utf8  DEFAULT COLLATE utf8_general_ci ;

CREATE TABLE IF NOT EXISTS `calendar`.`Absence` (
  `idAbsence` INT(11) NOT NULL,
  `date` DATE NOT NULL,
  `idReason` TINYINT(4) NOT NULL,
  PRIMARY KEY (`idAbsence`),
  INDEX `INDEX` (`idReason` ASC),
  CONSTRAINT `fx_AbsenceReason`
    FOREIGN KEY (`idReason`)
    REFERENCES `calendar`.`Reason` (`idReason`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `calendar`.`Reason` (
  `idReason` TINYINT(4) NOT NULL,
  `reason` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idReason`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


DELIMITER $$
USE `calendar`$$
CREATE PROCEDURE `addAbsence`(
  IN p_startDate DATE, 
  IN p_endDate DATE,
  IN p_idReason TINYINT
)
BEGIN
  DECLARE v_date DATE DEFAULT p_startDate;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;
  IF p_endDate < p_startDate THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'End date before start date';
  END IF;
 
  START TRANSACTION;
    REPEAT
      INSERT INTO 
        Absence 
      SET 
        idAbsence = NULL, 
        date = v_date, 
        idReason = p_idReason;
      SET v_date = DATE_ADD(v_date, INTERVAL 1 DAY);
    UNTIL v_date > p_endDate END REPEAT;
  COMMIT;
  SELECT 1 as `status`;
END$$

DELIMITER ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;