/******************************************************************************
  3 examples below query 1 row and store results INTO DECLARED variables
  You can only select a single row 
******************************************************************************/
/*====================================================================
  Selecting a single row into multiple declared variables
=====================================================================*/
DROP PROCEDURE proc_3a;
/
CREATE PROCEDURE proc_3a()
BEGIN
    DECLARE text_string   VARCHAR(50);
    DECLARE student_count INTEGER;

    SELECT 'total number of students', COUNT(*)
      INTO text_string, student_count
      FROM student
     WHERE fname = 'David';

    SELECT text_string, CONCAT('is... ', student_count) AS num_of_students;
END;
/
CALL proc_3a();
/



/*====================================================================
  Using input parameters
=====================================================================*/
DROP PROCEDURE proc_3b;
/
CREATE PROCEDURE proc_3b(p_name VARCHAR(20))
BEGIN
    DECLARE student_count INTEGER;

    SELECT COUNT(*)
      INTO student_count
      FROM student
     WHERE lname LIKE p_name
        OR fname LIKE p_name;

    SELECT CONCAT('Num of students matching ',p_name,' is: ',student_count) 
           AS num_of_students; 
END;
/
CALL proc_3b('%m%');
/


/*=====================================================================
  Using input and output parameters
  The output param is passed a global variable to deposit the value in
======================================================================*/
DROP PROCEDURE proc_3c;
/
CREATE PROCEDURE proc_3c(IN p_lname VARCHAR(20), OUT p_result INT)
BEGIN
-- ----------------------------------------------
-- Param: p_lname varchar(20), OUT p_result INT 
-- ----------------------------------------------
    SELECT COUNT(*)
      INTO p_result
      FROM student
     WHERE lname like p_lname;
END;
/
CALL proc_3c('%m%', @p_result);			-- passing a global variable
/
SELECT 'The number of matching students', @p_result;