RETURN Example

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