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.
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 |
... | ... |
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 |
... | ... |