/******************************************************************************
  The example below is an unbounded SELECT
  It return the result set to the calling program
  You can select a single row or multiple rows
  You cannot perform more than one select, if so, only the first is returned
******************************************************************************/
/*====================================================================
    Selecting multiple rows with an input parameter 
=====================================================================*/
DROP PROCEDURE tuition;
/
CREATE PROCEDURE tuition(p_amount INT)
BEGIN
-- ----------------------
-- Param: p_amount INT  
-- ----------------------
    SELECT student_id, fname, lname, sum( ifnull(price,0) ) as tuition
      FROM student LEFT JOIN class ON ssn = stu_ssn
                   LEFT JOIN course using(course_id) 
     GROUP BY(student_id) 
    HAVING tuition >= p_amount; 
END;
/
CALL tuition(2000);