CODE EXAMPLE FOR SQL

mysql procedure

BEGIN
   -- Declare loop constructs --
   DECLARE done INT DEFAULT FALSE;

   DECLARE my_tr_id VARCHAR(50);
   DECLARE my_start_date VARCHAR(50);
   DECLARE my_end_date VARCHAR(50);
   DECLARE my_bits VARCHAR(50);
   DECLARE error_status INT DEFAULT TRUE;


-- cursor --
declare cr cursor for 
SELECT tr_id, start_date, end_date, bits
FROM app_cronjob_tracker
WHERE division = 'OTC'
AND job_name = 'hqsales'
AND bits = 1
AND status = '1';



-- Declare Continue Handler --
DECLARE CONTINUE HANDLER FOR NOT FOUND 
SET
   done = TRUE;
OPEN cr;

CALL app_cron_status('OTC','Checking Status','Checking cronjob status [ app_cronjob_tracker ]','success',CURRENT_TIMESTAMP);

read_loop: LOOP -- Fetch data from cursor --
FETCH cr INTO my_tr_id,
my_start_date,
my_end_date,
my_bits;

-- Exit loop if finished --
IF done THEN
   
   LEAVE read_loop;
END IF;

SET error_status = FALSE;

SELECT my_tr_id,my_start_date,my_end_date,my_bits;

DELETE FROM app_temp_hqsales_otc where month = MONTH(my_end_date) AND year = YEAR(my_end_date);

CALL db_task_otc(my_start_date,my_end_date);

DELETE FROM app_temp_product_dbsales_otc where month = MONTH(my_end_date) AND year = YEAR(my_end_date);

CALL db_task_otc_db(my_start_date,my_end_date);

UPDATE app_cronjob_tracker SET bits = 0 WHERE tr_id = my_tr_id;



END
LOOP read_loop;
CLOSE cr;

IF error_status THEN
CALL app_cron_status('OTC','Checking Status','No active cronjob is found','success',CURRENT_TIMESTAMP);
ELSE 
CALL app_cron_status('OTC','Stage 1','Data inserted into temporary table ( app_temp_hqsales_otc )','Success',CURRENT_TIMESTAMP);
END IF;

END