SQL Stored Procedure to Loop Through All Test Names Between Defined Dates

Posted on the March 2nd, 2008 under Databases, Featured by cwebster 

DELIMITER $$

DROP PROCEDURE IF EXISTS `activity`.`calc_stats_all_tests` $$
CREATE PROCEDURE `activity`.`calc_stats_all_tests` (IN Start_Date DATE, IN End_date DATE)
BEGIN

  declare done INT DEFAULT 0;
  declare c_SetName VARCHAR(50);

DECLARE cur1 CURSOR FOR

SELECT s.`SetCode` FROM setparameters s;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

DROP TABLE IF EXISTS `activity`.`stats`;
CREATE TABLE  `activity`.`stats` (
  `SetName` varchar(50) NOT NULL,
  `HrsIn` double NOT NULL,
  `DateReceived` datetime NOT NULL,
  `AVGTurnaround` double NOT NULL,
  `NumberOfRequests` INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  open cur1;

  repeat
        fetch cur1 into c_SetName;
        if not done then
          CALL calcstats(c_SetName, Start_Date, End_Date);
        end if;
  until done end repeat;

close cur1;

END $$

DELIMITER ;

You must be logged in to post a comment.