I have the following SP:
DROP PROCEDURE IF EXISTS PitcherAvailability;
CREATE PROCEDURE PitcherAvailability(IN WK INT)
BEGIN
/***********************************************************************
Declaring variables needed.
***********************************************************************/
DECLARE lpid1, lpct1, lage1, lrest1, ljersey1 INT;
DECLARE lnextavail1 datetime;
DECLARE lgamedate1 datetime;
DECLARE pname1 VARCHAR(50);
DECLARE snextavail VARCHAR(50);
DECLARE done INT DEFAULT 0;
/***********************************************************************
DECLARE a CURSOR to get playerid's, pitchcounts, playername and jersey
for the week number passed to the PROCEDURE.
***********************************************************************/
DECLARE cur1 CURSOR FOR (SELECT p.playerid,
p.pitchcount,
l.playername,
l.age,
l.jersey,
g.gamedate
FROM games g
JOIN pitchcounts p on g.gamenum = p.gamenum
JOIN players l ON p.playerid = l.playerid
WHERE WEEK(g.gamedate) = WK);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET done = 1;
/**********************************************************************
Create a temp table to hold the values for this wk
***********************************************************************/
CREATE TEMPORARY TABLE IF NOT EXISTS `playeravail` (
`lpid` tinyint(3) unsigned NOT NULL default '0',
`lpct` tinyint(3) unsigned default '0',
`lage` tinyint(3) unsigned default '0',
`lrest` tinyint(3) unsigned default '0',
`lnextavail` datetime default NULL,
`ljersey` varchar(3) default NULL,
PRIMARY KEY (`lpid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/***********************************************************************
Now, OPEN the CURSOR and loop through the CURSOR and determine each
player's availability. Depending on the player's age, we handle the
availability differently.
***********************************************************************/
OPEN cur1;
REPEAT
FETCH cur1 INTO lpid1, lpct1, pname1, lage1, ljersey1, lgamedate1;
IF NOT done THEN
CASE lage1
WHEN 8 THEN
IF (lpct1 <=20) THEN
SET lrest1 = 0;
ELSEIF (lpct1 > 20 AND lpct1 <= 40) THEN
SET lrest1 = 1;
ELSEIF (lpct1 > 40 AND lpct1 <= 60) THEN
SET lrest1 = 2;
ELSEIF (lpct1 > 60 AND lpct1 <= 75) THEN
SET lrest1 = 3;
END IF;
WHEN 9 THEN
IF (lpct1 <= 20) THEN
SET lrest1 = 0;
ELSEIF (lpct1 > 20 AND lpct1 <= 40) THEN
SET lrest1 = 1;
ELSEIF (lpct1 > 40 AND lpct1 <= 60) THEN
SET lrest1 = 2;
ELSEIF (lpct1 > 60 AND lpct1 <= 75) THEN
SET lrest1 = 3;
END IF;
WHEN 10 THEN
IF (lpct1 <= 20) THEN
SET lrest1 = 0;
ELSEIF (lpct1 > 20 AND lpct1 <= 40) THEN
SET lrest1 = 1;
ELSEIF (lpct1 > 40 AND lpct1 <= 60) THEN
SET lrest1 = 2;
ELSEIF (lpct1 > 60 AND lpct1 <= 75) THEN
SET lrest1 = 3;
END IF;
WHEN 11 THEN
IF (lpct1 <= 20) THEN
SET lrest1 = 0;
ELSEIF (lpct1 > 20 AND lpct1 <= 40) THEN
SET lrest1 = 1;
ELSEIF (lpct1 > 40 AND lpct1 <= 60) THEN
SET lrest1 = 2;
ELSEIF (lpct1 > 60 AND lpct1 <= 85) THEN
SET lrest1 = 3;
END IF;
WHEN 12 THEN
IF (lpct1 <= 20) THEN
SET lrest1 = 0;
ELSEIF (lpct1 > 20 AND lpct1 <= 40) THEN
SET lrest1 = 1;
ELSEIF (lpct1 > 40 AND lpct1 <= 60) THEN
SET lrest1 = 2;
ELSEIF (lpct1 > 60 AND lpct1 <= 85) THEN
SET lrest1 = 3;
END IF;
WHEN 13 THEN
IF (lpct1 <= 20) THEN
SET lrest1 = 0;
ELSEIF (lpct1 > 20 AND lpct1 <= 40) THEN
SET lrest1 = 1;
ELSEIF (lpct1 > 40 AND lpct1 <= 60) THEN
SET lrest1 = 2;
ELSEIF (lpct1 > 60 AND lpct1 <= 95) THEN
SET lrest1 = 3;
END IF;
WHEN 14 THEN
IF (lpct1 <= 20) THEN
SET lrest1 = 0;
ELSEIF (lpct1 > 20 AND lpct1 <= 40) THEN
SET lrest1 = 1;
ELSEIF (lpct1 > 40 AND lpct1 <= 60) THEN
SET lrest1 = 2;
ELSEIF (lpct1 > 60 AND lpct1 <= 95) THEN
SET lrest1 = 3;
END IF;
WHEN 15 THEN
IF (lpct1 <= 20) THEN
SET lrest1 = 0;
ELSEIF (lpct1 > 20 AND lpct1 <= 40) THEN
SET lrest1 = 1;
ELSEIF (lpct1 > 40 AND lpct1 <= 60) THEN
SET lrest1 = 2;
ELSEIF (lpct1 > 60 AND lpct1 <= 95) THEN
SET lrest1 = 3;
END IF;
WHEN 16 THEN
IF (lpct1 <= 20) THEN
SET lrest1 = 0;
ELSEIF (lpct1 > 20 AND lpct1 <= 40) THEN
SET lrest1 = 1;
ELSEIF (lpct1 > 40 AND lpct1 <= 60) THEN
SET lrest1 = 2;
ELSEIF (lpct1 > 60 AND lpct1 <= 95) THEN
SET lrest1 = 3;
END IF;
END CASE;
END IF;
/***********************************************************************
Set the lnextavail variable to the gamedate + lrest value and insert
the record into the temporary table, playeravail
***********************************************************************/
SET lnextavail1 = DATE_ADD(lgamedate1, INTERVAL lrest1 DAY);
INSERT INTO playeravail (lpid, lpct, lage, lrest, lnextavail, ljersey) VALUES (lpid1, lpct1, lage1, lrest1, lnextavail1, ljersey1);
UNTIL done END REPEAT;
CLOSE cur1;
/***********************************************************************
Select and return all records from temporary table, playeravail
set all to NULL as a precaution
***********************************************************************/
SET lpid1 = NULL;
SET lpct1 = NULL;
SET lage1 = NULL;
SET lrest1 = NULL;
SET snextavail = NULL;
SET ljersey1 = NULL;
SELECT pa.lpid, pa.lpct, pa.lage, pa.lrest, DATE_FORMAT(pa.lnextavail, '%a %b %d, %Y'), pa.ljersey
INTO lpid1, lpct1, lage1, lrest1, snextavail, ljersey1
FROM playeravail pa;
END;
Oh crap, there is more than one result row so I can't return the results using variables, huh?
How do I do that?
