MySQL Procedure Parameters

Master MySQL stored procedure parameters — the three modes IN, OUT, and INOUT with worked examples on the employees table. Covers parameter syntax, why OUT/INOUT need user variables at the call site, the absence of default parameter values in MySQL, and how to choose the right mode for each scenario.

Parameters make stored procedures reusable. Instead of hard-coding values, you accept them as inputs, optionally write outputs, and let callers vary the behavior. MySQL parameters work very differently from regular function arguments in most languages because of parameter modes — three of them: IN, OUT, and INOUT.

Three pieces:

• Mode — IN (default if omitted), OUT, or INOUT.
• Name — follows the same rules as column names.
• Data type — any MySQL type: INT, VARCHAR(50), DECIMAL(10,2), DATE, etc.

Multiple parameters are separated by commas inside the parentheses after the procedure name.

The examples reuse the employees table. Set it up once if needed:

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    employee_id     INT UNSIGNED PRIMARY KEY,
    first_name      VARCHAR(20),
    last_name       VARCHAR(25)    NOT NULL,
    salary          DECIMAL(10, 2) NOT NULL,
    department_id   INT
);

INSERT INTO employees VALUES
    (100, 'Steven',    'King',      24000.00,  9),
    (101, 'Neena',     'Kochhar',   17000.00,  9),
    (102, 'Lex',       'De Haan',   17000.00,  9),
    (103, 'Alexander', 'Hunold',     9000.00,  6),
    (104, 'Bruce',     'Ernst',      6000.00,  6),
    (105, 'David',     'Austin',     4800.00,  6),
    (106, 'Valli',     'Pataballa',  4800.00,  6),
    (107, 'Diana',     'Lorentz',    4200.00,  6),
    (108, 'Nancy',     'Greenberg', 12000.00, 10),
    (109, 'Daniel',    'Faviet',     9000.00, 10);

Find all employees with a specific salary. The salary value comes in via an IN parameter:

DROP PROCEDURE IF EXISTS emp1;

DELIMITER $$

CREATE PROCEDURE emp1(IN sal DECIMAL(10, 2))
    READS SQL DATA
BEGIN
    SELECT employee_id, first_name, last_name, salary, department_id
    FROM   employees
    WHERE  salary = sal;
END$$

DELIMITER ;

CALL emp1(17000);

Two employees earn exactly 17,000 — Neena and Lex. The IN parameter sal received the value 17000 from the caller.

Count employees at a given salary, return the count via an OUT parameter:

DROP PROCEDURE IF EXISTS emp2;

DELIMITER $$

CREATE PROCEDURE emp2(IN  sal   DECIMAL(10, 2),
                      OUT total INT)
    READS SQL DATA
BEGIN
    SELECT COUNT(*)
    INTO   total
    FROM   employees
    WHERE  salary = sal;
END$$

DELIMITER ;

-- Call: provide a literal for IN, a user variable for OUT
CALL emp2(17000, @total);
SELECT @total;

The procedure wrote 2 into total, which the caller reads via @total. Notice that the CALL itself doesn't return a result set — the answer is in the user variable.

Take a salary value, increase it by 10%, write the result back through the same INOUT parameter, and update the database:

DROP PROCEDURE IF EXISTS emp3;

DELIMITER $$

CREATE PROCEDURE emp3(IN    emp_id INT,
                      INOUT salary DECIMAL(10, 2))
    MODIFIES SQL DATA
BEGIN
    -- Apply the 10% raise to the parameter directly
    SET salary = salary * 1.10;

    -- Persist the new salary
    UPDATE employees
    SET    salary = salary       -- NB: refers to the parameter
    WHERE  employee_id = emp_id;
END$$

DELIMITER ;

-- Caller hands in the current salary, gets back the new one
SET @salary = 24000;
CALL emp3(100, @salary);
SELECT @salary;

Started at 24,000, multiplied by 1.10 → 26,400. The new value flowed back to the caller through the INOUT parameter, and Steven King's row in the database was updated to match.

1. Default to IN for read-only inputs. Most parameters fall into this category.
2. Match parameter types to column types — same data type and length. Avoids implicit conversion bugs.
3. Don't shadow column names with parameter names when you can help it. WHERE salary = sal is fine; WHERE salary = salary with a parameter also named salary is ambiguous and resolves to "where the column equals itself" — i.e. always true. Prefix parameters (p_salary, i_salary) or keep names distinct.
4. OUT and INOUT need user variables at the call site. You'll have to declare and pass them; the caller can't supply literals.
5. For multiple return values, prefer a single result-set SELECT a, b, c over many OUT parameters — easier to consume from application code.
6. Document parameters with COMMENT when their meaning isn't obvious. amount is fine; p_x is not.
7. Validate inputs early. A procedure that does UPDATE accounts SET balance = balance - amount WHERE id = sender with no check for amount > 0 is one bug away from a disaster.

• Stored procedure parameters have three modes: IN (caller → procedure, default), OUT (procedure → caller), INOUT (both directions).
• OUT and INOUT parameters need user variables at the call site so the caller has somewhere to read the result.
• OUT parameters start as NULL inside the procedure — the procedure can't read the caller's initial value through them.
• MySQL has no default parameter values — every argument must be supplied at every call. Workaround: accept NULL and check inside the body.
• For returning many rows use a plain SELECT in the body (result set); for a single value use an OUT parameter; for transform-in-place semantics use INOUT.
• Match parameter types to column types, prefix parameters to avoid name shadowing, and validate inputs early.