Returning results from procedures

User's Guide
PART 1. Working with Databases
CHAPTER 10. Using Procedures, Triggers, and Batches

Returning results from procedures

Procedures can return results that are a single row of data, or multiple rows. In the former case, results can be passed back as arguments to the procedure. In the latter case, results are passed back as result sets. Procedures can also return a single value given in the RETURN statement.

For simple examples of how to return results from procedures, see Introduction to procedures. For more detailed information, see the following sections.

Returning a value using the RETURN statement

A single value can be returned to the calling environment using the RETURN statement, which causes an immediate exit from the procedure. The RETURN statement takes the form:

RETURN expression

The value of the supplied expression is returned to the calling environment. To save the return value in a variable, an extension of the CALL statement is used:

CREATE VARIABLE returnval INTEGER ;
returnval = CALL myproc() ;

Returning results as procedure parameters

Procedures can return results to the calling environment in the parameters to the procedure.

Within a procedure, parameters and variables can be assigned values in one of the following ways:

The parameter can be assigned a value using the SET statement.
The parameter can be assigned a value using a SELECT statement with an INTO clause.

Using the SET statement

The following somewhat artificial procedure returns a value in an OUT parameter that is assigned using a SET statement:

CREATE PROCEDURE greater (   IN a INT, 
                           IN b INT, 
                           OUT c INT)
BEGIN
   IF a > b THEN
      SET c = a;
   ELSE
      SET c = b;
   END IF ;
END

Using single-row SELECT statements

Single-row queries retrieve at most one row from the database. This type of query is achieved by a SELECT statement with an INTO clause. The INTO clause follows the select list and precedes the FROM clause. It contains a list of variables to receive the value for each select list item. There must be the same number of variables as there are select list items.

When a SELECT statement is executed, the server retrieves the results of the SELECT statement and places the results in the variables. If the query results contain more than one row, the server returns an error. For queries returning more than one row, cursors must be used. For information about returning more than one row from a procedure, see Returning result sets from procedures.

If the query results in no rows being selected, a row not found warning is returned.

The following procedure returns the results of a single-row SELECT statement in the procedure parameters.

To return the number of orders placed by a given customer:

Type the following:

CREATE PROCEDURE OrderCount (IN customer_ID INT,
                              OUT Orders INT)
BEGIN
   SELECT COUNT(dba.sales_order.id) 
      INTO Orders
   FROM dba.customer
      KEY LEFT OUTER JOIN "dba".sales_order 
   WHERE dba.customer.id = customer_ID;
END

You can test this procedure in Interactive SQL using the following statements, which show the number of orders placed by the customer with ID 102:

CREATE VARIABLE orders INT;
CALL OrderCount ( 102, orders );
SELECT orders;

Notes

The customer_ID parameter is declared as an IN parameter. This parameter holds the customer ID that is passed in to the procedure.
The Orders parameter is declared as an OUT parameter. It holds the value of the orders variable that is returned to the calling environment.
No DECLARE statement is required for the Orders variable, as it is declared in the procedure argument list.
The SELECT statement returns a single row and places it into the variable Orders.

Returning result sets from procedures

If a procedure returns more than one row of results to the calling environment, it does so using result sets.

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

CREATE PROCEDURE ListCustomerValue ()
RESULT ("Company" CHAR(36), "Value" INT)
BEGIN
   SELECT company_name, 
      CAST( sum(   sales_order_items.quantity * 
                  product.unit_price)
                  AS INTEGER ) AS value
   FROM customer
      INNER JOIN sales_order 
      INNER JOIN sales_order_items 
      INNER JOIN product 
   GROUP BY company_name 
   ORDER BY value DESC;
END

Type the following:
```
CALL ListCustomerValue ()
```

Company

Value

Chadwicks

8076

Overland Army Navy

8064

Martins Landing

6888

Sterling & Co.

6804

Carmel Industries

6780

...

...

Company	Value
Chadwicks	8076
Overland Army Navy	8064
Martins Landing	6888
Sterling & Co.	6804
Carmel Industries	6780
...	...

Notes

The number of variables in the RESULT list must match the number of the SELECT list items. Automatic data type conversion is carried out where possible if data types do not match.
The RESULT clause is part of the CREATE PROCEDURE statement, and is not followed by a command delimiter.
The names of the SELECT list items do not need to match those of the RESULT list.
When testing this procedure, Interactive SQL opens a cursor to handle the results. The cursor is left open following the SELECT statement, in case the procedure returns more than one result set. You should type RESUME to complete the procedure and close the cursor.
Procedure result sets are modifiable, unless they are generated from a view. The user calling the procedure requires the appropriate permissions on the underlying table in order to modify procedure results. This differs from the usual permissions for procedure execution, where the procedure owner must have permissions on the table.

Returning multiple result sets from procedures

A procedure can return more than one result set to the calling environment. If a RESULT clause is employed, the result sets must be compatible: they must have the same number of items in the SELECT lists, and the data types must all be of types that can be automatically converted to the data types listed in the RESULT list.

The following procedure lists the names of all employees, customers, and contacts listed in the database:

CREATE PROCEDURE ListPeople()
RESULT ( lname CHAR(36), fname CHAR(36) )
BEGIN
   SELECT emp_lname, emp_fname 
   FROM employee;
   SELECT lname, fname 
   FROM customer;
   SELECT last_name, first_name 
   FROM contact;
END

Notes

To test this procedure in Interactive SQL, enter the following statement:
```
CALL ListPeople () 
```
You must enter a RESUME statement after each of the three result sets is displayed in the Interactive SQL Data window to continue, and then complete, the procedure.

Returning variable result sets from procedures

The RESULT clause is optional in procedures. Omitting the result clause allows you to write procedures that return different result sets, with different numbers or types of columns, depending on how they are executed.

If you are not using this feature of variable result sets, it is recommended that you employ a RESULT clause, for performance reasons.

For example, the following procedure returns two columns if the input variable is Y, but only one column otherwise:

CREATE PROCEDURE names( IN formal char(1))
BEGIN
   IF formal = 'y' THEN
      SELECT emp_lname, emp_fname 
      FROM employee
   ELSE
      SELECT emp_fname 
      FROM employee
   END IF
END

The use of variable result sets in procedures is subject to some limitations, depending on the interface used by the client application.

Embedded SQL You must DESCRIBE the procedure call after the cursor for the result set is opened, but before any rows are returned, in order to get the proper shape of result set.

For information about the DESCRIBE statement, see DESCRIBE statement.
ODBC Variable result set procedures can be used by ODBC applications. The proper description of the variable result sets is carried out by the Adaptive Server Anywhere ODBC driver.
Open Client applications Variable result set procedures can be used by Open Client applications. The proper description of the variable result sets is carried out by Adaptive Server Anywhere.
Interactive SQL Interactive SQL does not support variable result set procedures, and so cannot be used for testing this feature.