Ways to return 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 using:

the SET statement

The following procedure returns a value in an OUT parameter assigned using a SET statement. You must have the CREATE PROCEDURE system privilege to execute the following 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;
```
a SELECT statement with an INTO clause

A single-row query retrieves at most one row from the database. This type of query uses 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 executes, the database 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 database server returns an error. For queries returning more than one row, you must use cursors.

If the query results in no rows being selected, the variables are not updated, and a warning is returned.

You must have the appropriate SELECT privileges on the object to execute a SELECT statement.

Example 1: Create a procedure and select its results using a SELECT...INTO statement

Start Interactive SQL and connect to the SAP Sybase IQ sample database. You must have the CREATE PROCEDURE system privilege and either SELECT privilege on the Employees table or the SELECT ANY TABLE system privilege.
In the SQL Statements pane, execute the following statement to create a procedure (AverageSalary) that returns the average salary of employees as an OUT parameter:
```
CREATE PROCEDURE AverageSalary( OUT average_salary NUMERIC(20,3) )
BEGIN
   SELECT AVG( Salary )
   INTO average_salary
   FROM GROUPO.Employees;
END;
```
Create a variable to hold the procedure output. In this case, the output variable is numeric, with three decimal places.
```
CREATE VARIABLE Average NUMERIC(20,3);
```
Call the procedure using the created variable to hold the result:
```
CALL AverageSalary( Average );
```
If the procedure was created and run properly, the Interactive SQL Messages tab does not display any errors.
To inspect the value of the variable, execute the following statement:
```
SELECT Average;
```
Look at the value of the output variable Average. The Results tab in the Results pane displays the value 49988.623 for this variable, the average employee salary.

Example 2: Returning the results of a single-row SELECT statement

Start Interactive SQL and connect to the SAP Sybase IQ sample database. You must have the CREATE PROCEDURE system privilege and either SELECT privilege on the Customers table or the SELECT ANY TABLE system privilege.

Execute the following statement to return the number of orders placed by a given customer:

CREATE PROCEDURE OrderCount( 
   IN customer_ID INT,
   OUT Orders INT )
BEGIN
   SELECT COUNT(SalesOrders.ID)
      INTO Orders
   FROM GROUPO.Customers
      KEY LEFT OUTER JOIN SalesOrders
   WHERE Customers.ID = customer_ID;
END;

Test this procedure 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 for Example 2 –

The customer_ID parameter is declared as an IN parameter. This parameter holds the customer ID passed in to the procedure.
The Orders parameter is declared as an OUT parameter. It holds the value of the orders variable returned to the calling environment.
No DECLARE statement is necessary 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.