Return

Valid in: DBProc

The Return statement terminates a currently executing database procedure and gives control back to the calling application, and, optionally, returns a value. The return statement can only be used inside a database procedure. The statement terminates the procedure and returns control to the application. (The calling application resumes execution at the statement following execute procedure.)

The optional return_status returns a value to the calling application when the return statement executes. Return_status must be a non-null integer constant, variable, or parameter whose data type is comparable with the data type of the variable to which its value is assigned. If the return_status is not specified or if a return statement is not executed, the procedure returns 0 to the calling application.

The into clause of the execute procedure statement allows the calling application to retrieve the return_status once the procedure has finished executing.

Syntax

The Return statement has the following format:

RETURN [return_status];

Permissions

All users are permitted to use this statement.

Example: Return

The following database procedure example, emp_sales_rank, returns rows containing the employee ID, total sales, and rank of sales amongst current salesmen:

create procedure emp_sales_rank
    result row (int, int, money) as
declare
    sales_tot money;
    empid int;
    sales_rank int;
begin
    sales_rank = 0;
    for select e.empid, sum(s.sales) as sales_sum into :empid, :sales_tot
        from employee e, sales s
         where e.job = 'sales' and e.empid = s.empid
         group by e.empid order by sales_sum do
      sales_rank = sales_rank + 1;
      return row(:sales_rank, :empid, :tot_sales);
    endfor;
end"