SQL RETURN

What is the SQL RETURN statement?

RETURN exits a stored procedure or function and optionally supplies a value 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 RETURN:

SQL RETURN Full Explanation

RETURN is a control-flow statement used inside stored procedures, stored functions, and trigger bodies. When the statement executes, the current routine stops immediately and execution control is passed back to the caller. If the dialect supports it, an expression can be supplied so that the routine returns a specific scalar value (often an integer status code or the function result). In PostgreSQL, MySQL, and Oracle PL/SQL the expression becomes the function’s result. In SQL Server, the optional integer expression becomes the stored procedure’s status code. If no value is supplied, the routine returns NULL or zero depending on the dialect. RETURN cannot be used in ad-hoc SQL outside of a routine, and it only returns one scalar value. To return result sets, dialect-specific constructs such as RETURN QUERY (PostgreSQL) or SELECT statements must be used.

SQL RETURN Syntax

-- PostgreSQL / MySQL / Oracle function
RETURN expression;

-- Exit without value
RETURN;

SQL RETURN Parameters

expression (any scalar) - Optional. The value sent back to the caller.

Example Queries Using SQL RETURN

-- PostgreSQL example
CREATE OR REPLACE FUNCTION add_numbers(a INT, b INT)
RETURNS INT AS $$
BEGIN
  RETURN a + b; -- returns sum to caller
END;
$$ LANGUAGE plpgsql;

-- SQL Server example
CREATE PROCEDURE check_user (@user_id INT)
AS
BEGIN
  IF NOT EXISTS (SELECT 1 FROM dbo.users WHERE id = @user_id)
     RETURN 404; -- custom status code
  RETURN 0;      -- success status
END;
GO

-- MySQL example
DELIMITER //
CREATE FUNCTION get_tax(subtotal DECIMAL(10,2))
RETURNS DECIMAL(10,2)
BEGIN
  RETURN subtotal * 0.08;
END//
DELIMITER ;

Expected Output Using SQL RETURN

The routine stops executing
The caller receives the supplied scalar value or the dialect’s default (0 or NULL)
No further statements in the routine run after RETURN

Use Cases with SQL RETURN

Provide the result of a scalar user-defined function.
Exit a routine early on validation failures.
Supply status codes from SQL Server stored procedures.
Improve readability by avoiding deeply nested IF blocks.

Common Mistakes with SQL RETURN

Using RETURN in plain SQL outside a routine.
Expecting RETURN to send a full result set instead of a single scalar.
Forgetting to include RETURN in a function body, leading to NULL or unexpected defaults.
Assuming all dialects allow non-integer values in stored procedures (SQL Server only allows INT).

First Introduced In

Oracle PL/SQL (1992) and SQL Server 6.0 (1995)

Frequently Asked Questions

What is the difference between RETURN and RETURN NEXT?

RETURN stops the routine and returns a single scalar value. RETURN NEXT (PostgreSQL) appends a row to the function’s result set and continues execution.

Can I use RETURN in a plain SQL script?

No. RETURN only works inside stored procedures, functions, or trigger bodies. Using it elsewhere raises a syntax error.

What happens if I omit the expression after RETURN?

The routine exits immediately. SQL Server returns 0, PostgreSQL and MySQL return NULL, unless the function is declared with RETURNS void.

How do I return a full result set?

Use dialect-specific mechanisms such as SELECT statements inside the routine, output parameters, table-valued functions, or PostgreSQL’s RETURN QUERY.