Returning results from procedures

Chapter 20. 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 information about returning a single row, see "Returning results as procedure parameters". For information about returning multiple rows, see "Returning result sets from procedures".

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 database engine retrieves the results of the select statement and places the results in the variables. If the query results contain more than one row, the database engine 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

This procedure can be tested in ISQL 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 theOrders 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

...

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

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 ISQL, enter the statement CALL ListPeople (); You must enter a RESUME statement after each of the three result sets is displayed in the ISQL data window to continue, and then complete, the procedure.
A command delimiter is required after the first two SELECT statements. It is optional after the final statement in a statement list.

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 SQL Anywhere ODBC driver.
Open Client applications Variable result set procedures can be used by Open Client applications using the Open Server Gateway. The proper description of the variable result sets is carried out by the Open Server Gateway.
ISQL ISQL does not support calling of variable result set procedures, and so cannot be used for testing this feature.

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