MySQL Stored Procedure with IN and OUT Parameters

[lovasoa]

A MySQL Stored Procedure is useful when you need to run multiple SQL statements as a unit, especially when logic like branching, loops, or variable handling is involved. It can take input (IN) and return results using output (OUT) parameters.

Example with 1 IN and 2 OUT parameters:

This procedure updates a user's last login time and returns their email and account status.

DELIMITER //

CREATE PROCEDURE UpdateLoginAndFetchInfo(
  IN userId INT,
  OUT userEmail VARCHAR(100),
  OUT accountStatus VARCHAR(20)
)
BEGIN
  UPDATE users
  SET last_login = NOW()
  WHERE id = userId;

  SELECT email, status
  INTO userEmail, accountStatus
  FROM users
  WHERE id = userId;
END //

DELIMITER ;

Call it

You cannot use MySQL variables directly from SQLPage.
Depending on what your use case is, you can

• Use a function instead of a procedure, and make that function return a JSON object. Then, you can use that object using MySQL's json processing functions
• Make the procedure create a temporary table. Then call the procedure without OUT parameters, and select values from the temporary table.