Information returned in result sets from procedures

In addition to returning results to the calling environment in individual parameters, procedures can return information in result sets. A result set is typically the result of a query.

The number of variables in the RESULT clause must match the number of the SELECT list items. Automatic data type conversion is performed where possible if data types do not match.

The RESULT clause is part of the CREATE PROCEDURE statement, and does not have a statement delimiter.

The names of the SELECT list items do not need to match those in the RESULT clause.

To modify procedure result sets on a view, the user must have the appropriate privileges on the underlying table.

In the case that a stored procedure or user-defined function returns a result, it cannot also support output parameters or return values.

Interactive SQL displays only the first result set by default. To allow a procedure to return more than one row of results in Interactive SQL, set the Show Multiple Result Sets option on the Results tab of the Options window.

Example 1

The following procedure returns a list of customers who have placed orders, together with the total value of the orders placed.

Execute the following statement in Interactive SQL:

CREATE PROCEDURE ListCustomerValue()
RESULT ( "Company" CHAR(36), "Value" INT )
BEGIN
   SELECT CompanyName,
      CAST( SUM(  SalesOrderItems.Quantity *
                  Products.UnitPrice )
                  AS INTEGER ) AS value
   FROM Customers
      INNER JOIN SalesOrders
      INNER JOIN SalesOrderItems
      INNER JOIN Products
   GROUP BY CompanyName
   ORDER BY value DESC;
END;

Executing CALL ListCustomerValue ( ); returns the following result set:

Company	Value
The Hat Company	5016
The Igloo	3564
The Ultimate	3348
North Land Trading	3144
Molly's	2808
...	...

Example 2

The following procedure returns a result set containing the salary for each employee in a given department. Execute the following statement in Interactive SQL:

CREATE PROCEDURE SalaryList( IN department_id INT )
RESULT ( "Employee ID" INT, Salary NUMERIC(20,3) )
BEGIN
   SELECT EmployeeID, Salary
    FROM Employees
    WHERE Employees.DepartmentID = department_id;
END;

The names in the RESULT clause are matched to the results of the query and used as column headings in the displayed results.

To list the salaries of employees in the R & D department (department ID 100), execute the following statement:

CALL SalaryList( 100 );

The following result set appears in the Results pane:

Employee ID	Salary
102	45700.000
105	62000.000
160	57490.000
243	72995.000
...	...