MySQL Parameter Modes: IN, OUT, and INOUT

When creating stored procedures in MySQL, you can specify how parameters behave using three parameter modes: IN, OUT, and INOUT. Each mode determines how data flows between the calling program and the stored procedure.

IN Parameters

IN is the default parameter mode if none is specified.

Characteristics:

• The procedure receives a copy of the parameter's value
• Changes to the parameter inside the procedure are not visible to the caller
• The parameter can be a literal value, a variable, or an expression

Example:

DELIMITER //
CREATE PROCEDURE calculate_bonus(IN employee_id INT, IN bonus_percentage DECIMAL(5,2))
BEGIN
    -- Update employee's bonus based on their salary
    UPDATE employees
    SET bonus = salary * (bonus_percentage / 100)
    WHERE id = employee_id;

    -- Any modifications to employee_id or bonus_percentage here
    -- will not affect the original variables in the calling program
    SET employee_id = 0; -- This change is only visible within the procedure
END//
DELIMITER ;

-- Calling the procedure
SET @emp_id = 101;
SET @bonus_pct = 10.5;
CALL calculate_bonus(@emp_id, @bonus_pct);

-- @emp_id still equals 101, it was not changed by the procedure
SELECT @emp_id; -- Will show 101

OUT Parameters

OUT parameters allow the procedure to return values to the caller.

Characteristics:

• The procedure does not receive any initial value from the caller
• Initial value inside the procedure is NULL
• Changes to the parameter inside the procedure are visible to the caller
• Must be a variable, not a literal or expression

Example:

DELIMITER //
CREATE PROCEDURE get_employee_info(IN emp_id INT, OUT emp_name VARCHAR(100), OUT emp_salary DECIMAL(10,2))
BEGIN
    -- Retrieve employee information
    SELECT name, salary INTO emp_name, emp_salary
    FROM employees
    WHERE id = emp_id;

    -- If no employee found, variables remain NULL
END//
DELIMITER ;

-- Calling the procedure
SET @id = 101;
SET @name = '';
SET @salary = 0;
CALL get_employee_info(@id, @name, @salary);

-- @name and @salary now contain values set by the procedure
SELECT @name, @salary;

INOUT Parameters