Procedure Parameters
In this page:
IN Parameters
IN parameters are the default parameter type and let you pass values into a procedure for it to read and use, such as an ID used to filter which rows the procedure works on — the caller's original value is never modified.
Example: IN Parameters
DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;
CALL GetUserById(1);OUT Parameters
OUT parameters flow in the opposite direction, letting a procedure hand a computed value — like a row count or a status code — back to whatever called it, without the caller needing to pass anything meaningful in first.
Example: OUT Parameters
DELIMITER //
CREATE PROCEDURE CountUsers(OUT total INT)
BEGIN
SELECT COUNT(*) INTO total FROM users;
END //
DELIMITER ;
CALL CountUsers(@total);
SELECT @total;INOUT Parameters
INOUT parameters combine both directions: the caller passes in a starting value, the procedure can read and change it internally, and the updated value is handed back once the procedure finishes.
Example: INOUT Parameters
DELIMITER //
CREATE PROCEDURE DoubleValue(INOUT n INT)
BEGIN
SET n = n * 2;
END //
DELIMITER ;
SET @val = 5;
CALL DoubleValue(@val);
SELECT @val;Mixing Parameter Types
A single procedure can mix IN, OUT, and INOUT parameters together freely, giving you fine control over exactly which values flow in, which flow out, and which do both.
Example: Mixing Parameter Types
DELIMITER //
CREATE PROCEDURE AdjustStock(IN product_id INT, INOUT stock INT, OUT status TEXT)
BEGIN
SET stock = stock - 1;
SET status = IF(stock > 0, 'ok', 'out of stock');
END //
DELIMITER ;Handling Missing Values in Parameters
Every parameter defined on a procedure must be supplied when it's called, whether as a literal value or a variable — leaving one out causes MySQL to raise an error rather than silently using a default.
Example: Handling Missing Values in Parameters
DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;
-- CALL GetUserById(); -- errors: missing required parameter
CALL GetUserById(1);Chapter Quiz — Complete all 5 topics to unlock
0/5 topics done
Complete these topics first: