SQL in a Nutshell, 3rd Edition

Name

RETURN Statement

The RETURN statement terminates processing within a SQL-invoked function (as opposed to a host-invoked function) or stored procedure and returns the routine’s result value.

Tip

Some vendors use RETURNS instead of RETURN (the SQL standard).

Platform	Command
MySQL	Supported, with limitations
Oracle	Supported
PostgreSQL	Supported, with limitations
SQL Server	Supported

SQL2003 Syntax

RETURN return_parameter_value | NULL

Keywords

return_parameter_value

Represents a value returned by the routine code. A wide variety of value types are possible.

NULL

Terminates the function without returning an actual value.

Rules at a Glance

Use the RETURN statement within procedural code to terminate processing. For example, you might create a user-defined function that takes a complex and often-used CASE expression and, when passed a parameter, returns a single, easy-to-understand expression value.

Programming Tips and Gotchas

Although the RETURN statement is categorized as a separate command within SQL, it is deeply intertwined with the CREATE FUNCTION and CREATE PROCEDURE statements. Consequently, the RETURN statement is almost always found embedded in one of these other commands. Check each statement’s section in this book, or your vendor documentation, to get a more complete understanding of each platform’s implementation of RETURN within the context of each statement.

MySQL

MySQL supports the ANSI-standard syntax for RETURN, excluding the NULL keyword:

RETURNS return_parameter_value

Oracle

Oracle supports ...