SQL INOUT

What is SQL INOUT?

INOUT marks a stored procedure or function parameter that is both supplied by the caller and returned with any modifications.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL INOUT: PostgreSQL 8.1+, MySQL 5.0+, MariaDB, Oracle PL/SQL (as IN OUT). Not supported in SQLite. SQL Server uses OUTPUT for similar behavior.

SQL INOUT Full Explanation

INOUT is a parameter mode used in stored procedures and user-defined functions. When a parameter is declared INOUT, the caller passes an initial value (input), the routine can read and change that value, and the final value is written back to the caller (output). INOUT therefore combines the behavior of IN and OUT modes.Key points:- Scope: Only valid inside CREATE FUNCTION and CREATE PROCEDURE statements.- Passing mechanism: Most engines pass INOUT parameters by reference; changes inside the routine are visible after the call completes.- Return type: In PostgreSQL an INOUT parameter also contributes to the routine’s composite result set, so RETURNS can often be omitted. In MySQL and MariaDB, INOUT parameters do not affect the routine’s explicit RETURN value (procedures have none).- NULL handling: If the caller passes NULL, the routine can still assign a non-NULL value before returning.- Privileges: The caller must have EXECUTE permission on the routine and rights to set the variable receiving the output.

SQL INOUT Syntax

-- PostgreSQL function
CREATE FUNCTION swap_values(INOUT a integer, INOUT b integer)
AS $$
BEGIN
  a := a + b;
  b := a - b;
  a := a - b;
END;
$$ LANGUAGE plpgsql;

-- MySQL procedure
DELIMITER //
CREATE PROCEDURE add_tax(INOUT amount DECIMAL(10,2), IN rate DECIMAL(5,2))
BEGIN
  SET amount = amount * (1 + rate);
END//
DELIMITER ;

SQL INOUT Parameters

param_name identifier - variable that receives the value
param_type data type - any valid scalar or composite type
DEFAULT (expression) - optional default (PostgreSQL only)
Note - The INOUT keyword itself takes no arguments.

Example Queries Using SQL INOUT

-- PostgreSQL call
SELECT * FROM swap_values(3, 7);
-- Returns a=7, b=3

-- MySQL session
SET @total = 100.00;
CALL add_tax(@total, 0.075);
SELECT @total; -- 107.50

Expected Output Using SQL INOUT

#VALUE!

Use Cases with SQL INOUT

Swapping or incrementing variables inside one call
Returning multiple values without defining a composite type
Updating monetary totals, counters, or status flags in place
Simplifying APIs where a parameter must be both read and updated

Common Mistakes with SQL INOUT

Forgetting to prefix the parameter with INOUT and thus only receiving input
Expecting INOUT to work in plain SQL statements; it is limited to routine definitions
Omitting a variable to capture the returned value in MySQL, resulting in lost output
Assuming SQL Server supports INOUT (it uses OUTPUT instead)

First Introduced In

PostgreSQL 8.1 / MySQL 5.0

Frequently Asked Questions

What is the difference between IN, OUT, and INOUT?

IN is read-only input, OUT is write-only output, and INOUT performs both roles so the parameter value can be read and modified.

Can I use INOUT in a plain SELECT statement?

No. INOUT is valid only within CREATE FUNCTION or CREATE PROCEDURE definitions.

How many INOUT parameters can a routine have?

As many as needed, provided the database’s maximum parameter count is not exceeded.

Does an INOUT parameter contribute to the return type in PostgreSQL?

Yes. PostgreSQL treats each INOUT parameter as part of the returned composite row set.