/******************************************************************************
  The IF statement
******************************************************************************/
DROP PROCEDURE if_proc;
/
CREATE PROCEDURE if_proc(p_student_id INT)
BEGIN
-- =============================
-- Param: p_student_id INT
-- =============================
    DECLARE v_sex 	CHAR(1);
    DECLARE v_name	VARCHAR(20);
    DECLARE v_result	VARCHAR(25);

    SELECT sex, lname 
      INTO v_sex, v_name 
      FROM student 
     WHERE student_id = p_student_id;

    IF (v_sex = 'M' and v_name < 'N') THEN 
    	    SET v_result = 'Male/lower alphabet'; 
        ELSEIF (v_sex = 'M' and v_name >= 'N') THEN 
            SET v_result = 'Male/upper alphabet'; 
        ELSEIF (v_sex = 'F' and v_name <  'N') THEN 
            SET v_result = 'Female/lower alphabet'; 
        ELSEIF (v_sex = 'F' and v_name >= 'N') THEN 
            SET v_result = 'Female/upper alphabet'; 
	ELSE
	    SET v_result = 'unknown'; 
    END IF;
    SET @name   = v_name;
    SET @sex    = v_sex;
    SET @result = v_result;
END;

/
CALL if_proc(5);

/
select @name, @sex, @result;