SQL OUT

What is the SQL OUT parameter?

Marks a procedure or function parameter as output-only so the called routine can pass a value back to the caller.

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 OUT: MySQL, MariaDB, PostgreSQL, Oracle, SQL Server (as OUTPUT), IBM Db2. Not supported in SQLite and some serverless warehouses.

SQL OUT Full Explanation

OUT is a parameter mode defined in the SQL/PSM standard and implemented by most major databases. When a parameter is declared OUT, the called stored procedure or function can assign a value to it, and that value is returned to the caller when execution finishes. The caller must supply a variable, placeholder, or session variable that can receive the output.Key points:- OUT parameters cannot be read inside the routine until they are assigned. They are write-only from the caller’s standpoint.- A routine may declare multiple OUT parameters, allowing it to return several scalar values without building a result set.- OUT differs from INOUT: INOUT lets the caller pass an initial value that the routine can also modify.- In SQL Server the equivalent keyword is OUTPUT, but the concept is identical.- OUT is not available in SQLite and some cloud data warehouses that do not support stored procedures.Caveats:- Forgetting to set an OUT parameter leaves it NULL (or database default), which can cause confusion.- Client libraries differ in how they bind and retrieve OUT parameters; check driver documentation.- OUT parameters are scoped to the call and are not visible across concurrent sessions unless stored in a user session variable.

SQL OUT Syntax

-- Generic SQL/PSM
CREATE PROCEDURE procedure_name (
    OUT param_name data_type
) 
BEGIN
    -- statements that set param_name
END;

-- PostgreSQL function version
CREATE FUNCTION function_name(OUT param_name data_type)
RETURNS data_type AS $$
BEGIN
    -- statements that set param_name
END;
$$ LANGUAGE plpgsql;

SQL OUT Parameters

Example Queries Using SQL OUT

-- MySQL example
DELIMITER //
CREATE PROCEDURE get_user_email(IN p_user_id INT, OUT p_email VARCHAR(255))
BEGIN
    SELECT email INTO p_email
    FROM users
    WHERE id = p_user_id;
END//
DELIMITER ;

CALL get_user_email(42, @email);
SELECT @email;  -- returns the email for user 42

-- PostgreSQL example
CREATE FUNCTION totals_and_count(items int[])
RETURNS TABLE(total INT, item_count INT) AS $$
BEGIN
    SELECT SUM(i), COUNT(*) INTO total, item_count
    FROM unnest(items) AS t(i);
END;
$$ LANGUAGE plpgsql;

SELECT * FROM totals_and_count(ARRAY[1,2,3]);

Expected Output Using SQL OUT

The first call stores the user’s email in the session variable @email
The SELECT then returns that value
The PostgreSQL function returns a single row containing the total and count calculated inside the function

Use Cases with SQL OUT

Return multiple scalar values from a procedure without building a complex result set.
Expose status flags or generated keys (e.g., new ID) to the caller.
Supply calculated aggregates (sum, avg, count) while also returning a regular result set.
Provide error codes or messages separate from main query output.

Common Mistakes with SQL OUT

Declaring OUT but never assigning a value.
Attempting to read an OUT parameter inside the procedure before it is set.
Forgetting that OUT parameters are NULL by default.
Mixing up OUT and INOUT and expecting the input value to be preserved.
Using OUT in databases such as SQLite that do not support stored procedures.

First Introduced In

SQL:1999 (SQL/PSM)

Frequently Asked Questions

What databases support OUT parameters?

MySQL, PostgreSQL, Oracle, SQL Server (as OUTPUT), MariaDB, IBM Db2. SQLite does not support them.

Can I have multiple OUT parameters in one procedure?

Yes. You can declare any number of OUT parameters, separated by commas in the parameter list.

What happens if I do not set an OUT parameter?

It returns NULL (or database default). Always assign a value to avoid surprises.

How is OUT different from RETURN?

RETURN sends a single scalar value from a function, while OUT can send multiple values and is available in both procedures and functions.