Stored Procedures Sample

create_user_applied_count_tbl.sql

USE [DATABASE NAME];
DROP procedure IF EXISTS `[PROCEDURES NAME]`;

DELIMITER $$
USE [TABLE NAME]$$

CREATE DEFINER=`[USER NAME]`@`[HOST NAME]` PROCEDURE `[PROCEDURES NAME]`(IN targetDate date)
BEGIN

  /* VARIABLE FOR CURSOR */
  DECLARE eventId INT;
  /* FOR CURSOR END */
  DECLARE done INT DEFAULT FALSE;
  /* GET EVENT ID FROM EVENT TABLE */
  DECLARE cur CURSOR FOR 
  SELECT  id
  FROM    event;
        
  /* SET CURSOR HANDLER FOR END */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  /* SQL ERROR HANDLER */
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SELECT 'An error has occurred, operation the stored procedure was terminated';
  END;
  
  /************************
    CREATE TEMPORARY TABLE
  ************************/
  DROP TEMPORARY TABLE IF EXISTS tmp_daily_applied_user_count;
    
  /* CREATE SQL DYNAMICALLY */
  SET @createTmp = "CREATE TEMPORARY TABLE tmp_daily_applied_user_count (clearDate DATE";
  OPEN cur;
    SET done = FALSE;
    read_loop: LOOP
    FETCH cur INTO eventId;
    /* IF CURSOR FINISHED THEN LEAVE */
    IF done THEN
      LEAVE read_loop;
    END IF;
    
    SET @createTmp = concat(@createTmp, ", event_", eventId, " INT");
               
    END LOOP;
  CLOSE cur;
           
  SET @createTmp = concat(@createTmp, ")");
  PREPARE stmt FROM @createTmp;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
  SELECT * FROM tmp_daily_applied_user_count;

END
$$

DELIMITER ;