SQL in a Nutshell

Name

RETURN

Synopsis

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

Vendor	Command
SQL Server	Supported
MySQL	Supported
Oracle	Supported
PostgreSQL	Supported

SQL99 Syntax and Description

RETURNS return_parameter_value | NULL

The RETURN function is used within a function to end its processing. Using the NULL clause terminates the function without returning an actual value. Otherwise, the parameter value specified is returned either as a variable or as a literal expression.

Although the RETURN statement is categorized as a separate command within SQL, it is deeply intertwined with the CREATE FUNCTION statement. Check the CREATE FUNCTION statement for a more complete understanding of each vendor’s implementation of RETURN.

Examples

This example creates a function. The function returns the value that is stored in the proj_rev variable to the calling session:

CREATE FUNCTION project_revenue (project IN varchar2)
RETURN NUMBER
AS
   proj_rev NUMBER(10,2);
BEGIN
   SELECT SUM(DECODE(action,'COMPLETED',amount,0) -
          SUM(DECODE(action,'STARTED',amount,0)   +
          SUM(DECODE(action,'PAYMENT',amount,0)
   INTO proj_rev
   FROM construction_actions
   WHERE project_name = project;
   RETURN (proj_rev);
END;

This example creates a function that returns a calculated value to the calling session:

CREATE FUNCTION metric_volume -- Input dimensions in centimeters. (@length decimal(4,1), @width decimal(4,1), @height ...