[RESOLVED] Moving query to Stored Procedure

[mrbaseball34]

Need help writing the following stored procedure. The query returns the correct
data but how do I get the stored procedure to return the correct results? Also, when I
try to run the create code in HeidiSQL, it gives me errors, see below...

[code]

-- Error message

You have an error in your SQL syntax; check...for the right
syntax to use near 'DELIMITER $$
CREATE PROCEDURE PitcherAvailability(WK INT)
' at line 1

--

-- This is the expected result of the Stored Procedure

lpid, lpct, lage, lrest, lnextavail, ljersey
1, 10, 8, 1, "Sun Apr 22, 2007", "34"
2, 20, 8, 0, "Sat Apr 21, 2007", "21"
24, 15, 8, 0, "Sat Apr 21, 2007", "22"
14, 30, 12, 1, "Sat Apr 21, 2007", "42"
13, 35, 13, 1, "Sun Apr 22, 2007", " 2"
16, 40, 12, 2, "Mon Apr 23, 2007", " 4"

[/code]

--
-- Stored procedure
--
DELIMITER $$
CREATE PROCEDURE PitcherAvailability(WK INT)
  /*********************************************************************** 
   Declaring variables needed.
  ***********************************************************************/
  DECLARE lpid, lpct, lage, lrest, ljersey INT$$
  DECLARE lnextavail datetime$$
  DECLARE pname VARCHAR(50)$$

  /**********************************************************************
   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$$


  /*********************************************************************** 
   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
                           FROM games g
                           JOIN pitchcounts p on g.gamenum = p.gamenum
                           JOIN players l ON p.playerid = l.playerid
                           WHERE WEEK( g.gamedate ) = WK)$$

  /*********************************************************************** 
   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 lpid, lpct, pname, lage, ljersey
    IF NOT done THEN
     CASE lage
      WHEN  8 THEN
        IF (lpct <=20) THEN
          SET lrest = 0$$
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1$$
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2$$
        IF (lpct > 60 AND lpct <= 75) THEN
          SET lrest = 3$$
      WHEN  9 THEN 
        IF (lpct <= 20) THEN
          SET lrest = 0$$
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1$$
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2$$
        IF (lpct > 60 AND lpct <= 75) THEN
          SET lrest = 3$$
      WHEN 10 THEN
        IF (lpct <= 20) THEN
          SET lrest = 0$$
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1$$
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2$$
        IF (lpct > 60 AND lpct <= 75) THEN
          SET lrest = 3$$
      WHEN 11 THEN
        IF (lpct <= 20) THEN
          SET lrest = 0$$
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1$$
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2$$
        IF (lpct > 60 AND lpct <= 85) THEN
          SET lrest = 3$$
      WHEN 12 THEN
        IF (lpct <= 20) THEN
          SET lrest = 0$$
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1$$
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2$$
        IF (lpct > 60 AND lpct <= 85) THEN
          SET lrest = 3$$
      WHEN 13 THEN
        IF (lpct <= 20) THEN
          SET lrest = 0$$
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1$$
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2$$
        IF (lpct > 60 AND lpct <= 95) THEN
          SET lrest = 3$$
      WHEN 14 THEN
        IF (lpct <= 20) THEN
          SET lrest = 0$$
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1$$
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2$$
        IF (lpct > 60 AND lpct <= 95) THEN
          SET lrest = 3$$
      WHEN 15 THEN
        IF (lpct <= 20) THEN
          SET lrest = 0$$
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1$$
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2$$
        IF (lpct > 60 AND lpct <= 95) THEN
          SET lrest = 3$$
      WHEN 16 THEN
        IF (lpct <= 20) THEN
          SET lrest = 0$$
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1$$
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2$$
        IF (lpct > 60 AND lpct <= 95) THEN
          SET lrest = 3$$
      END CASE$$
    END IF$$
    /*********************************************************************** 
      Set the lnextavail variable to the gamedate + lrest value and insert
      the record into the temporary table, playeravail
    ***********************************************************************/
    SET lnextavail = DATE_ADD(CURRDATE(), INTERVAL lrest DAY)$$
    INSERT INTO playeravail (lpid, lpct, lage, lrest, lnextavail, ljersey) VALUES (lpid, lpct, lage, lrest, lnextavail, ljersey)$$
  UNTIL done END$$
  /*********************************************************************** 
   Select and return all records from temporary table, playeravail
  ***********************************************************************/
  SELECT lpid, lpct, lage, lrest, DATE_FORMAT(lnextavail, '%a %b %d, %Y'), ljersey from playeravail
END$$
DELIMITER ;

[mrbaseball34]

Here's the Database dump:

[code]

-- Table games

DROP TABLE IF EXISTS games;
CREATE TABLE IF NOT EXISTS games (
gameid int(11) NOT NULL auto_increment,
gamenum int(11) NOT NULL default '0',
gamedate datetime NOT NULL default '0000-00-00 00:00:00',
visitorid int(11) NOT NULL default '0',
homeid int(11) NOT NULL default '0',
visitorscore int(11) default NULL,
homescore int(11) default NULL,
fieldid int(11) NOT NULL default '0',
modified tinyint(6) default NULL,
forfeit tinyint(4) default NULL,
practice tinyint(11) default NULL,
xref int(11) default NULL,
PRIMARY KEY (gameid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=39 ;

INSERT INTO games VALUES (1, 1, '2007-04-02 12:00:00', 1, 5, 10, 12, 1, NULL, 1, 0, 0);
INSERT INTO games VALUES (2, 2, '2007-04-02 13:00:00', 2, 2, NULL, NULL, 1, NULL, 0, 1, NULL);
INSERT INTO games VALUES (3, 3, '2007-04-02 16:00:00', 6, 11, 4, 2, 4, NULL, 0, 0, 0);
INSERT INTO games VALUES (4, 4, '2007-04-02 10:00:00', 3, 19, 999, 999, 4, 1, 0, 0, 12);
INSERT INTO games VALUES (5, 5, '2007-04-02 13:00:00', 31, 31, NULL, NULL, 5, NULL, 0, 0, NULL);
INSERT INTO games VALUES (6, 6, '2007-04-02 16:00:00', 9, 6, NULL, NULL, 5, NULL, 0, 0, NULL);
INSERT INTO games VALUES (7, 7, '2007-04-24 00:00:00', 28, 29, 8, 6, 3, NULL, 0, 0, NULL);
INSERT INTO games VALUES (8, 8, '2007-04-26 00:00:00', 26, 27, 1, 9, 1, NULL, 0, 0, NULL);
INSERT INTO games VALUES (9, 9, '2007-04-12 12:00:00', 2, 1, 0, 2, 5, 1, 0, 0, NULL);
INSERT INTO games VALUES (11, 11, '2007-04-12 15:00:00', 1, 5, NULL, NULL, 6, NULL, 0, 0, NULL);
INSERT INTO games VALUES (12, 12, '2007-04-12 16:00:00', 3, 19, 4, 0, 5, 1, 0, 0, 4);
INSERT INTO games VALUES (13, 13, '2007-04-12 17:00:00', 5, 4, NULL, NULL, 6, NULL, 0, 0, NULL);
INSERT INTO games VALUES (14, 14, '2007-04-12 18:00:00', 31, 31, 0, NULL, 1, NULL, 0, 0, NULL);
INSERT INTO games VALUES (15, 15, '2007-04-12 20:00:00', 9, 8, 0, 2, 1, NULL, 0, 0, NULL);
INSERT INTO games VALUES (16, 16, '2007-04-13 00:00:00', 11, 10, 0, 3, 1, NULL, 0, 0, NULL);
INSERT INTO games VALUES (17, 17, '2007-04-13 14:00:00', 8, 6, 0, 1, 1, NULL, 0, 0, NULL);
INSERT INTO games VALUES (18, 18, '2007-04-13 16:00:00', 9, 7, 0, 2, 1, NULL, 0, 0, NULL);
INSERT INTO games VALUES (19, 19, '2007-04-14 16:00:00', 11, 10, 0, 3, 1, NULL, 0, 0, NULL);
INSERT INTO games VALUES (20, 20, '2007-04-15 16:00:00', 13, 12, 0, 3, 1, NULL, 0, 0, NULL);
INSERT INTO games VALUES (21, 21, '2007-04-17 00:00:00', 15, 14, 0, 2, 1, NULL, 0, 0, NULL);
INSERT INTO games VALUES (22, 22, '2007-04-18 00:00:00', 17, 16, 0, 3, 1, NULL, 0, 0, NULL);
INSERT INTO games VALUES (23, 23, '2007-04-18 13:00:00', 15, 12, 0, 1, 1, NULL, 0, 0, NULL);
INSERT INTO games VALUES (24, 24, '2007-04-18 14:00:00', 16, 13, 0, 4, 2, NULL, 0, 0, NULL);
INSERT INTO games VALUES (25, 25, '2007-04-19 14:00:00', 17, 14, 0, 3, 0, NULL, 0, 0, NULL);
INSERT INTO games VALUES (26, 26, '2007-04-20 13:00:00', 18, 19, 8, 6, 1, NULL, 0, 0, NULL);
INSERT INTO games VALUES (27, 27, '2007-04-20 16:00:00', 20, 21, 0, 12, 1, NULL, 0, 0, NULL);
INSERT INTO games VALUES (28, 28, '2007-04-21 00:00:00', 22, 23, 0, 25, 1, NULL, 0, 0, NULL);
INSERT INTO games VALUES (29, 29, '2007-04-21 14:00:00', 18, 20, NULL, NULL, 1, NULL, 0, 0, NULL);
INSERT INTO games VALUES (30, 30, '2007-04-23 00:00:00', 24, 25, 1, 2, 4, NULL, 0, 0, NULL);
INSERT INTO games VALUES (31, 31, '2007-04-23 16:00:00', 26, 27, 1, 2, 4, NULL, 0, 0, NULL);
INSERT INTO games VALUES (32, 32, '2007-04-25 00:00:00', 28, 29, 6, 1, 4, NULL, 0, 0, NULL);

--

-- Table pitchcounts

DROP TABLE IF EXISTS pitchcounts;
CREATE TABLE IF NOT EXISTS pitchcounts (
id int(11) NOT NULL auto_increment,
gamenum int(11) NOT NULL,
playerid int(11) NOT NULL,
pitchcount tinyint(4) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table to save Pitcher/Pitchcounts' AUTO_INCREMENT=7 ;

INSERT INTO pitchcounts VALUES (1, 29, 1, 10);
INSERT INTO pitchcounts VALUES (2, 29, 2, 20);
INSERT INTO pitchcounts VALUES (3, 29, 24, 15);
INSERT INTO pitchcounts VALUES (4, 29, 14, 30);
INSERT INTO pitchcounts VALUES (5, 29, 13, 35);
INSERT INTO pitchcounts VALUES (6, 29, 16, 40);

--

-- Table players

DROP TABLE IF EXISTS players;
CREATE TABLE IF NOT EXISTS players (
playerid int(11) NOT NULL auto_increment,
teamid int(11) NOT NULL,
jersey varchar(3) default NULL,
playername varchar(255) NOT NULL,
age tinyint(4) default NULL,
KEY playerid (playerid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=59 ;

INSERT INTO players VALUES (1, 18, '34', 'Nolan Ryan', 8);
INSERT INTO players VALUES (2, 18, '21', 'Roger Clemens', 8);
INSERT INTO players VALUES (3, 18, '51', 'Randy Johnson', 9);
INSERT INTO players VALUES (5, 18, '40', 'David Wells', 12);
INSERT INTO players VALUES (6, 18, '56', 'Brad Lidge', 15);
INSERT INTO players VALUES (7, 18, '55', 'Chad Qualls', 9);
INSERT INTO players VALUES (8, 18, '17', 'Roy Oswalt', 10);
INSERT INTO players VALUES (9, 18, '46', 'Andy Pettitte', 11);
INSERT INTO players VALUES (10, 18, '26', 'Mariano Rivera', 13);
INSERT INTO players VALUES (11, 20, '34', 'Nolan Ryan', 9);
INSERT INTO players VALUES (12, 20, '21', 'Roger Clemens', 10);
INSERT INTO players VALUES (13, 20, '2', 'Randy Johnson', 13);
INSERT INTO players VALUES (14, 20, '42', 'Bob Gibson', 12);
INSERT INTO players VALUES (15, 20, '19', 'David Wells', 13);
INSERT INTO players VALUES (16, 20, '4', 'Brad Lidge', 12);
INSERT INTO players VALUES (17, 20, '55', 'Chad Qualls', 15);
INSERT INTO players VALUES (18, 20, '68', 'Roy Oswalt', 16);
INSERT INTO players VALUES (19, 20, '41', 'Andy Pettitte', 13);
INSERT INTO players VALUES (20, 20, '26', 'Mariano Rivera', 9);
INSERT INTO players VALUES (24, 18, '22', 'justin', 14);

--

-- Table teams

DROP TABLE IF EXISTS teams;
CREATE TABLE IF NOT EXISTS teams (
id int(1) NOT NULL auto_increment,
teamname varchar(20) NOT NULL default '',
division char(2) NOT NULL default '',
email varchar(45) NOT NULL default '',
active tinyint(4) NOT NULL default '1',
PIN int(4) NOT NULL default '1990',
link varchar(255) default NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id),
KEY teamname (teamname)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=32 ;

INSERT INTO teams VALUES (1, 'Blue Jays', 'AA', '[e-mail]', 1, 1990, 'http://www.bluejays.com');
INSERT INTO teams VALUES (2, 'Orioles', 'AA', '[e-mail]', 1, 2000, 'http://www.orioles.com');
INSERT INTO teams VALUES (3, 'Yankees', 'AA', '[e-mail]', 1, 2010, NULL);
INSERT INTO teams VALUES (4, 'A''s', 'AA', '[e-mail]', 1, 2020, NULL);
INSERT INTO teams VALUES (5, 'White Sox', 'AA', '[e-mail]', 1, 2030, NULL);
INSERT INTO teams VALUES (6, 'Giants', 'C', '[e-mail]', 1, 2040, NULL);
INSERT INTO teams VALUES (7, 'Braves', 'C', '[e-mail]', 1, 2050, NULL);
INSERT INTO teams VALUES (8, 'Phillies', 'C', '[e-mail]', 1, 2060, NULL);
INSERT INTO teams VALUES (9, 'Mets', 'C', '[e-mail]', 1, 2070, NULL);
INSERT INTO teams VALUES (10, 'Pirates', 'C', '[e-mail]', 1, 2080, NULL);
INSERT INTO teams VALUES (11, 'Dodgers', 'C', '[e-mail]', 1, 2090, NULL);
INSERT INTO teams VALUES (12, 'Falcons', 'N', '[e-mail]', 1, 2100, NULL);
INSERT INTO teams VALUES (13, 'Ravens', 'N', '[e-mail]', 1, 2110, NULL);
INSERT INTO teams VALUES (14, 'Hawks', 'N', '[e-mail]', 1, 2120, NULL);
INSERT INTO teams VALUES (15, 'Thrashers', 'N', '[e-mail]', 1, 2130, NULL);
INSERT INTO teams VALUES (16, 'Owls', 'N', '[e-mail]', 1, 2140, NULL);
INSERT INTO teams VALUES (17, 'Herons', 'N', '[e-mail]', 1, 2150, NULL);
INSERT INTO teams VALUES (18, 'Penguins', 'A', '[e-mail]', 1, 2160, NULL);
INSERT INTO teams VALUES (19, 'Roadrunners', 'A', '[e-mail]', 1, 2170, NULL);
INSERT INTO teams VALUES (20, 'Cardinals', 'A', '[e-mail]', 1, 2180, NULL);
INSERT INTO teams VALUES (21, 'Kingbirds', 'A', '[e-mail]', 1, 2190, NULL);
INSERT INTO teams VALUES (22, 'Condors', 'A', '[e-mail]', 1, 2200, NULL);
INSERT INTO teams VALUES (23, 'Vultures', 'A', '[e-mail]', 1, 2210, NULL);
INSERT INTO teams VALUES (24, 'Holland 2', 'BR', '[e-mail]', 1, 2220, NULL);
INSERT INTO teams VALUES (25, 'Holland 1', 'BR', '[e-mail]', 1, 2230, NULL);
INSERT INTO teams VALUES (26, 'Buckeyes', 'BR', '[e-mail]', 1, 2240, 'http://www.buckeys.com');
INSERT INTO teams VALUES (27, 'Cains', 'BR', '[e-mail]', 1, 2250, NULL);
INSERT INTO teams VALUES (28, 'Gators', 'BR', '[e-mail]', 1, 2260, NULL);
INSERT INTO teams VALUES (29, 'Noles', 'BR', '[e-mail]', 1, 2270, NULL);
INSERT INTO teams VALUES (30, 'Dogs', 'BR', '[e-mail]', 1, 2280, NULL);
INSERT INTO teams VALUES (31, 'OPEN', '', '', 1, 1990, NULL);
[/code]

[MarkR]

In order to create stored procedures using this syntax, you should run the mysql command line interface. You can still use a fancy editor to edit the .sql file.

Little or nothing else will understand the "DELIMITER $$".

You also need to use a ; within the procedure and a $$ at the end of the definition.

Have you actually tried doing something a little bit simpler first?

I think you should try to walk before you run. How about "Hello, World" ?

Try it all out on a non-production box first. Ensure that your development machine has the same software set (OS, web server, PHP, MySQL) as production.

Mark

[mrbaseball34]

The code above has flaws, below is the modified code, however, in running the code below in mysql CLI, I get this error:
"ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'DECLARE cur1 CURSOR FOR SELECT p.playerid,
p.p' at line 25"

Any ideas??

DELIMITER $$
CREATE PROCEDURE PitcherAvailability(WK INT)
BEGIN
  /*********************************************************************** 
    Declaring variables needed.
  ***********************************************************************/
  DECLARE lpid INT;
  DECLARE lpct INT; 
  DECLARE lage INT; 
  DECLARE lrest INT; 
  DECLARE ljersey INT;
  DECLARE lnextavail datetime;
  DECLARE pname VARCHAR(50);

  /*********************************************************************** 
   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;


/*********************************************************************** 
   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
                           FROM games g
                           JOIN pitchcounts p on g.gamenum = p.gamenum
                           JOIN players l ON p.playerid = l.playerid
                           WHERE WEEK( g.gamedate ) = WK;

  /*********************************************************************** 
   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 lpid, lpct, pname, lage, ljersey
    IF NOT done THEN
     CASE lage
      WHEN  8 THEN
        IF (lpct <=20) THEN
          SET lrest = 0;
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1;
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2;
        IF (lpct > 60 AND lpct <= 75) THEN
          SET lrest = 3;
      WHEN  9 THEN 
        IF (lpct <= 20) THEN
          SET lrest = 0;
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1;
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2;
        IF (lpct > 60 AND lpct <= 75) THEN
          SET lrest = 3;
      WHEN 10 THEN
        IF (lpct <= 20) THEN
          SET lrest = 0;
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1;
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2;
        IF (lpct > 60 AND lpct <= 75) THEN
          SET lrest = 3;
      WHEN 11 THEN
        IF (lpct <= 20) THEN
          SET lrest = 0;
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1;
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2;
        IF (lpct > 60 AND lpct <= 85) THEN
          SET lrest = 3;
      WHEN 12 THEN
        IF (lpct <= 20) THEN
          SET lrest = 0;
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1;
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2;
        IF (lpct > 60 AND lpct <= 85) THEN
          SET lrest = 3;
      WHEN 13 THEN
        IF (lpct <= 20) THEN
          SET lrest = 0;
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1;
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2;
        IF (lpct > 60 AND lpct <= 95) THEN
          SET lrest = 3;
      WHEN 14 THEN
        IF (lpct <= 20) THEN
          SET lrest = 0;
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1;
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2;
        IF (lpct > 60 AND lpct <= 95) THEN
          SET lrest = 3;
      WHEN 15 THEN
        IF (lpct <= 20) THEN
          SET lrest = 0;
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1;
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2;
        IF (lpct > 60 AND lpct <= 95) THEN
          SET lrest = 3;
      WHEN 16 THEN
        IF (lpct <= 20) THEN
          SET lrest = 0;
        IF (lpct > 20 AND lpct <= 40) THEN
          SET lrest = 1;
        IF (lpct > 40 AND lpct <= 60) THEN
          SET lrest = 2;
        IF (lpct > 60 AND lpct <= 95) THEN
          SET lrest = 3;
      END CASE;
    END IF;
    /*********************************************************************** 
      Set the lnextavail variable to the gamedate + lrest value and insert
      the record into the temporary table, playeravail
    ***********************************************************************/
    SET lnextavail = DATE_ADD(CURRDATE(), INTERVAL lrest DAY);
    INSERT INTO playeravail (lpid, lpct, lage, lrest, lnextavail, ljersey) VALUES (lpid, lpct, lage, lrest, lnextavail, ljersey);
  UNTIL done END;
  /*********************************************************************** 
   Select and return all records from temporary table, playeravail
  ***********************************************************************/
  SELECT lpid, lpct, lage, lrest, DATE_FORMAT(lnextavail, '%a %b %d, %Y'), ljersey from playeravail
END;
$$
DELIMITER ;

[mrbaseball34]

This is the latest code:

DELIMITER $$
DROP PROCEDURE IF EXISTS PitcherAvailability $$
CREATE PROCEDURE PitcherAvailability(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 $$
DELIMITER ;

However, I'm having trouble getting to the result set.

Anyone know how to do it with PHP?
Running in a mySQL GUI (HeidiSQL) it doesn't return a result set but the playersavail table has the correct results.