Returning result sets from SQLJ procedures

You can develop SQLJ procedures that return result sets to the invoking routine or application. In SQLJ procedures, the returning of result sets is handled with ResultSet objects.

Procedure

To return a result set from an SQLJ procedure:

Declare an iterator class to handle query data. For example:

  #sql iterator SpServerEmployees(String, String, double);

For each result set that is to be returned, include a parameter of type ResultSet[] in the procedure declaration. For example the following function signature accepts an array of ResultSet objects:

  public static void getHighSalaries(
    double inSalaryThreshold,         // double input
    int[] errorCode,                  // SQLCODE output
    ResultSet[] rs)                   // ResultSet output

Instantiate an iterator object. For example:
```
  SpServerEmployees c1;
```
Assign the SQL statement that will generate the result set to an iterator. In the following example, a host variable (called inSalaryThreshold -- refer to the previously shown function signature example) is used in the query's WHERE clause:
```
  #sql c1 = {SELECT name, job, CAST(salary AS DOUBLE)
               FROM staff
               WHERE salary > :inSalaryThreshold
               ORDER BY salary};
```
Execute the statement and get the result set:
```
  rs[0] = c1.getResultSet();
```

What to do next

If you have not done so already, develop a client application or caller routine that will accept result sets from your procedure.