-- Create log file to track commands and returns 
-- (Save results of commands to output file using tee command 
-- (Displays output of program and copies it into file):
-- mysql> tee logfile.txt (by default, saves logfile.txt to mysql/bin directory) 
-- mysql> notee (disables outfile write)

-- students: tee logfile.txt

-- BEGIN IN mode example -------------------------------
drop procedure if exists in_mode;
DELIMITER // 
CREATE PROCEDURE in_mode(IN par1 TINYINT) 
  BEGIN 
  -- at this point, par1 contains passed value, but doesn't pass back to calling environment
    set par1=par1 + par1;
    SELECT par1 as inside_in_mode_sp;
  END // 
DELIMITER ; 

-- can call using literal value
-- call in_mode(2);

/* explicit creation of session variable @arg1 */
SET @arg1=2;

/* display session variable's value before call */
SELECT @arg1 as before_call;

-- call using session variable
call in_mode(@arg1);

/* display session variable's value after call (IN mode: no value passed back) */
SELECT @arg1 as after_call;

drop procedure if exists in_mode;
-- END IN mode example -------------------------------

-- BEGIN OUT mode example -------------------------------
drop procedure if exists out_mode;
DELIMITER // 
CREATE PROCEDURE out_mode(OUT par1 TINYINT) 
  BEGIN 
  -- at this point, par1 contains null value, but passes back to calling environment (uncomment next line to demo)
    set par1=par1 + par1;
    SELECT par1 as inside_out_mode_sp;
  END // 
DELIMITER ; 

-- illegal: cannot use literal value when using OUT mode (must use variable)
-- call out_mode(2);

/* explicit creation of session variable @arg1 */
SET @arg1=2;

/* display session variable's value before call */
SELECT @arg1 as before_call;

-- call using session variable
call out_mode(@arg1);

/* display session variable's value after call (OUT mode: value passed back) */
SELECT @arg1 as after_call;

drop procedure if exists out_mode;
-- END OUT mode example -------------------------------

-- BEGIN INOUT mode example -------------------------------
drop procedure if exists inout_mode;
DELIMITER // 
CREATE PROCEDURE inout_mode(INOUT par1 TINYINT) 
  BEGIN 
  -- unlike OUT mode, par1 contains value passed by @arg1, and passes back to calling environment
    set par1=par1 + par1;
    SELECT par1 as inside_inout_mode_sp;
  END // 
DELIMITER ; 

-- illegal: cannot use literal value when using INOUT mode (must use variable)
-- call inout_mode(2);

/* explicit creation of session variable @arg1 */
SET @arg1=2;

/* display session variable's value before call */
SELECT @arg1 as before_call;

-- call using session variable
call inout_mode(@arg1);

/* display session variable's value after call (INOUT mode: value passed back) */
SELECT @arg1 as after_call;

drop procedure if exists inout_mode;
-- END INOUT mode example -------------------------------

-- notee
