/******************************************************************************
  4 examples below are unbounded SELECTs
  They return the results 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 a single row not from a database table 
=====================================================================*/
DROP PROCEDURE proc_2a;
/
CREATE PROCEDURE proc_2a()
BEGIN
    SELECT 'Hello World, this is my first procedure' AS "Greeting";
END;
/
CALL proc_2a();
/



/*====================================================================
  Selecting a single row from a database table
=====================================================================*/
DROP PROCEDURE proc_2b;
/
CREATE PROCEDURE proc_2b()
BEGIN
    SELECT 'total number of students', COUNT(*)
      FROM student
     WHERE fname = 'David';
END;
/
CALL proc_2b();
/



/*====================================================================
  Selecting multiple rows with an input parameter 
=====================================================================*/
DROP PROCEDURE proc_2c;
/
CREATE PROCEDURE proc_2c(p_student_id INT)
BEGIN
-- --------------------------
-- Param: p_student_id INT 
-- --------------------------
    SELECT student_id, fname, lname, sex
      FROM student
     WHERE student_id >= p_student_id;
END;
/
CALL proc_2c(10);
/



/*====================================================================
    Selecting multiple rows with an input parameter 
=====================================================================*/
DROP PROCEDURE proc_2d;
/
CREATE PROCEDURE proc_2d(IN p_lname VARCHAR(20))
BEGIN
-- ------------------------------
-- Param: p_lname varchar(20) 
-- ------------------------------
    SELECT *
      FROM student
     WHERE lname like p_lname
       AND sex = 'M';
END;
/
CALL proc_2d('%s%');
/