/******************************************************************************
  2 Examples of the CASE statement
******************************************************************************/
/*====================================================================
  This example is a simple CASE
=====================================================================*/
DROP PROCEDURE case1_proc;
/
CREATE PROCEDURE case1_proc(p_date DATE)
BEGIN
    DECLARE v_today   varchar(15);
    DECLARE v_message varchar(50);

    SET v_today = dayofweek(p_date);        -- p_date is the param
    CASE v_today
	    WHEN 1 THEN set v_message = 'Sunday, time to relax';
	    WHEN 2 THEN set v_message = 'Monday, first day of work week';
	    WHEN 3 THEN set v_message = 'Tuesday, second day of work week';
	    WHEN 4 THEN set v_message = 'Wednesday, it is midweek';
	    WHEN 5 THEN set v_message = 'Thursday, the weekend is almost here';
	    WHEN 6 THEN set v_message = 'Friday, thank God it is Friday';
	    WHEN 7 THEN set v_message = 'Saturday, the weekend is here';
    END CASE;

    SELECT v_message;
END;
/
CALL case1_proc(now())



/*====================================================================
  This example is a Searched CASE
=====================================================================*/
DROP PROCEDURE case2_proc;
/
CREATE PROCEDURE case2_proc(p_student_id INT)
BEGIN
    DECLARE v_result       varchar(25);
    DECLARE v_sex          char(1);
    DECLARE v_description  varchar(50);

    SELECT sex, description into v_sex, v_description 
      from student, class cl, course co
     where ssn=stu_ssn
       and cl.course_id = co.course_id
       and student_id   = p_student_id        -- p_student_id is the param
     limit 1;  

    CASE
	WHEN v_sex='M' and v_description like '%SQL%'
	    THEN set v_result = 'Male with SQL skills';
	WHEN v_sex='F' and v_description like '%SQL%'  
	    THEN set v_result = 'Female with SQL skills';
	WHEN v_sex='M' and v_description like '%Java%'
	    THEN set v_result = 'Male with Java skills';
	WHEN v_sex='F' and v_description like '%Java%'  
	    THEN set v_result = 'Female with Java skills';
	ELSE 	 set v_result = 'unknown';
    END CASE;

    SET @sex    = v_sex;
    SET @desc   = v_description;
    SET @result = v_result;
END;
/
CALL case2_proc(2);
/
SELECT @sex, @desc, @result;