MySQL OUT Parameter: IN, OUT and INOUT in Stored Procedures

A MySQL OUT parameter lets a stored procedure send a value back to the caller through a variable. IN carries a value in, OUT carries one back, and INOUT does both. This post tests all three, including the error a literal causes.

A Small Table to Test On

I ran every statement here on MariaDB 12.3. The syntax shown also works in MySQL, as its manual documents. The one exception is default values, covered near the end. The table holds six sales rows for three fruits.

CREATE DATABASE sqla_outparam;

USE sqla_outparam;

CREATE TABLE sales (
  id INT PRIMARY KEY AUTO_INCREMENT,
  item VARCHAR(30) NOT NULL,
  qty INT NOT NULL,
  price DECIMAL(6,2) NOT NULL
);

INSERT INTO sales (item, qty, price) VALUES
  ('mango', 3, 2.50),
  ('banana', 12, 0.30),
  ('mango', 5, 2.50),
  ('apple', 7, 0.80),
  ('mango', 2, 2.60),
  ('apple', 4, 0.80);

IN: The Default Mode

A parameter with no mode word is an IN parameter. The procedure receives a copy of the value, so nothing it does to the copy reaches the caller. You can pass a literal or a variable to an IN parameter. A procedure with one statement in its body needs no special setup.

CREATE PROCEDURE price_of(p_item VARCHAR(30)) SELECT MAX(price) AS top_price FROM sales WHERE item = p_item;

CALL price_of('mango');

A body with several statements needs a BEGIN … END block, and the block contains semicolons. The mysql and mariadb clients end a statement at the first semicolon. To avoid that, you change the client’s delimiter for a moment. DELIMITER is a client command, not SQL. An application sends the whole CREATE PROCEDURE text and never sends DELIMITER.

Here the procedure changes its own copy of a number. The caller’s variable stays as it was.

DELIMITER //
CREATE PROCEDURE bump(IN p_n INT)
BEGIN
  SET p_n = p_n + 1;
  SELECT p_n AS inside_value;
END//
DELIMITER ;

SET @n = 10;

CALL bump(@n);

SELECT @n;

The first table is what the procedure saw. The second is what the caller still has: 10.

OUT: Sending a Value Back

An OUT parameter works the other way. The procedure fills it, and the caller reads it afterward. You pass a user variable, which is a name that starts with @. It lives as long as your connection, and other connections can’t see it. The variable keeps its value until you change it or disconnect.

DELIMITER //
CREATE PROCEDURE item_total(IN p_item VARCHAR(30), OUT p_total INT)
BEGIN
  SELECT SUM(qty) INTO p_total FROM sales WHERE item = p_item;
END//
DELIMITER ;

CALL item_total('mango', @total);

SELECT @total;

Three mango sales of 3, 5 and 2 add up to 10. The SELECT … INTO statement is how the procedure puts the sum into the parameter.

Inside the procedure, an OUT parameter starts as NULL, whatever the caller had in the variable. This procedure reads its parameter before it sets it.

DELIMITER //
CREATE PROCEDURE out_peek(OUT p_x INT)
BEGIN
  SELECT p_x AS inside_value;
  SET p_x = 7;
END//
DELIMITER ;

SET @x = 99;

CALL out_peek(@x);

SELECT @x;

The 99 never arrived. The procedure saw NULL and left 7 behind. When you need a value to go in and come back changed, use INOUT.

One quiet case needs a decision. An item that was never sold makes SUM return NULL, so the caller gets NULL and not zero.

CALL item_total('durian', @total);

SELECT @total;

Decide what the caller should see for a missing item, and handle it inside the procedure. A procedure can also have several OUT parameters. A function returns one value, so this is the main reason to choose a procedure.

DELIMITER //
CREATE PROCEDURE item_stats(IN p_item VARCHAR(30), OUT p_qty INT, OUT p_revenue DECIMAL(8,2))
BEGIN
  SELECT SUM(qty), SUM(qty * price) INTO p_qty, p_revenue FROM sales WHERE item = p_item;
END//
DELIMITER ;

CALL item_stats('mango', @qty, @revenue);

SELECT @qty, @revenue;

Passing a Literal Fails

Passing a literal is the classic MySQL OUT parameter mistake, because the call looks right. An OUT parameter needs a place to put the answer. A literal such as 5 has no place, so the server refuses the call.

CALL item_total('mango', 5);

ERROR 1414 (42000): OUT or INOUT argument 2 for routine sqla_outparam.item_total is not a variable or NEW pseudo-variable in BEFORE trigger

The message counts arguments from one, so argument 2 is the 5. Replace it with a variable such as @total. The last words come from a trigger rule that you can ignore for procedures. The procedure itself was created without trouble. The server complains only at call time.

INOUT: In and Out

An INOUT parameter carries the caller’s value in and the changed value out. Take the revenue we measured and add 8 percent tax to it.

DELIMITER //
CREATE PROCEDURE add_tax(INOUT p_amount DECIMAL(8,2), IN p_percent INT)
BEGIN
  SET p_amount = ROUND(p_amount * (1 + p_percent / 100), 2);
END//
DELIMITER ;

CALL add_tax(@revenue, 8);

SELECT @revenue;

The value 25.20 went in and 27.22 came out. I wrapped the math in ROUND so the result fits two decimals. Without it, the same call finished with one warning.

A procedure can call another procedure. Inside the body, a local variable made with DECLARE works as the OUT target, and it needs no @ sign.

DELIMITER //
CREATE PROCEDURE report_item(IN p_item VARCHAR(30))
BEGIN
  DECLARE v_total INT;
  CALL item_total(p_item, v_total);
  SELECT p_item AS item, v_total AS total_qty;
END//
DELIMITER ;

CALL report_item('apple');

Reading the Modes Back

The server stores every parameter’s mode, even where you typed none. This query lists three of our procedures.

SELECT SPECIFIC_NAME, ORDINAL_POSITION, PARAMETER_MODE, PARAMETER_NAME, DTD_IDENTIFIER
FROM information_schema.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'sqla_outparam' AND SPECIFIC_NAME IN ('price_of', 'item_total', 'add_tax')
ORDER BY SPECIFIC_NAME, ORDINAL_POSITION;

The p_item parameter of price_of shows IN, although we never wrote the word.

Default Values

MySQL has no default values for procedure parameters. If you leave an argument out, the call fails.

CALL item_total('mango');

ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE sqla_outparam.item_total; expected 2, got 1

MariaDB 12.3 is different. It accepts DEFAULT on an IN parameter. This part works on MariaDB only, so skip it when the code must also run on MySQL. Code tested only on MariaDB can break on MySQL, because a call with a missing argument fails there.

CREATE PROCEDURE top_item(IN p_item VARCHAR(30) DEFAULT 'mango') SELECT p_item AS item, SUM(qty) AS total_qty FROM sales WHERE item = p_item;

CALL top_item();

CALL top_item('apple');

The first call used the default and returned the mango total. The second call returned apple with 11. MariaDB refuses the same clause on an OUT parameter with error 4032.

CREATE PROCEDURE out_default(OUT p_x INT DEFAULT 5) SET p_x = 1;

ERROR 4032 (HY000): Default/ignore value is not supported for such parameter usage

Or Use a Plain SELECT?

You could say OUT parameters are clumsy. A procedure can return a result set with a plain SELECT. A stored function returns one value that you can use inside a query. Fair point. A SELECT is the better choice when a person or an application reads rows. An OUT parameter earns its place when another procedure needs the value. A procedure that calls another one can’t read the rows of the inner SELECT. It also helps when the caller wants a few single values without parsing a result set.

The Same Ideas in SQL Server

The biggest difference is the call. SQL Server needs the word OUTPUT again in the EXEC statement. Leave it out and the value never reaches your variable. In MySQL the procedure definition already says which arguments are OUT, so the call needs nothing extra.

A Short Checklist

Keep these five rules in mind for every MySQL OUT parameter you write.

• Pass a variable for every OUT and INOUT argument, never a literal.
• Read the variable right after the CALL, on the same connection.
• Set an OUT parameter on every path, because it starts as NULL.
• Decide what NULL means to the caller, for example for an unknown item.
• Pass every argument, unless you use MariaDB and wrote a DEFAULT.

When you finish testing, drop the example database. That removes the table and every procedure in it.

DROP DATABASE sqla_outparam;

A MySQL OUT parameter is not a return value, it is a variable you hand the procedure to fill.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.