Returning Result Sets from SQL Stored Procedures with STOREPRCRS

Alright, let's get stuck in with stored procedures that actually return data. Most of the time when you call a stored procedure you want more than a single output parameter. You want a whole set of rows. STOREPRCRS shows you the clean, standard way to do exactly that on IBM i using SQLRPGLE.

This lesson walks you through building a stored procedure that accepts a department code and returns every matching employee as a result set. The caller (whether that is JDBC, ODBC, Python, Node or another RPG program) can then fetch as many rows as it needs.

What You Will Learn

By the end of this lesson you will know how to:

• Declare an SQL cursor that will become a result set
• Open the cursor so it stays available for the caller
• Use SET RESULT SETS to tell SQL this procedure returns data
• Pass input parameters into the cursor's WHERE clause using host variables
• Register the RPGLE program as a callable SQL stored procedure
• Consume the result set from Java, Python, Node and other clients
• Decide when result sets are the right choice over output parameters

All the code is practical SQLRPGLE that you can drop into your own environment and extend.

Why Result Sets Matter

Output parameters are fine for single values or even a single row. But the moment you need to return a list of anything (employees, orders, transactions, log entries) you hit limitations fast.

Result sets give you:

• Unlimited rows
• Dynamic column structure
• Standard SQL behaviour that every client understands
• Efficient, on-demand fetching instead of loading everything into memory up front

STOREPRCRS demonstrates the classic pattern you will use again and again.

The Core Pattern

Here is the fundamental three-step dance inside the procedure.

1. Declare the cursor that defines your result set.
2. Open the cursor (leave it open).
3. Tell SQL you are returning that cursor as a result set.

In code it looks like this:

rpgle

exec sql
 declare c1 cursor for
 select emp_id, emp_name, city, salary
 from nicklitten/employee_master
 where dept_code = :dept_code_in;

exec sql
 open c1;

exec sql
 set result sets cursor c1;

That is it. After the SET RESULT SETS statement you simply return from the procedure. The cursor stays open and SQL hands the result set back to whoever called you.

The Full Procedure

The example accepts one input parameter and returns four columns.

Parameters:

• dept_code_in (char(10) const) - the department you want to filter on

Result set columns:

• emp_id (int)
• emp_name (char(50))
• city (char(30))
• salary (dec(11,2))

You declare a pi for the parameter, declare and open the cursor using that host variable, then set the result set and return.

How to Call It

From straight SQL it could not be simpler:

SQL

CALL NICKLITTEN.STOREPRCRS('SALES');

The result set comes back automatically. Your SQL client or tool displays the rows.

Calling from Client Languages

This is where the power really shows.

Java (JDBC)

Java

CallableStatement cs = conn.prepareCall("CALL NICKLITTEN.STOREPRCRS(?)");
cs.setString(1, "SALES");
ResultSet rs = cs.executeQuery();

while (rs.next()) {
 System.out.println(rs.getInt("emp_id") + ": " + rs.getString("emp_name"));
}

Python (pyodbc)

Python

cursor.execute("CALL NICKLITTEN.STOREPRCRS(?)", 'SALES')
for row in cursor.fetchall():
 print(f"{row.emp_id}: {row.emp_name} - {row.city}")

Node.js (odbc)

JavaScript

const result = await connection.query(
 'CALL NICKLITTEN.STOREPRCRS(?)',
 ['SALES']
);
result.forEach(row => {
 console.log(`${row.EMP_ID}: ${row.EMP_NAME}`);
});

Every modern language that can talk to DB2 over ODBC or JDBC handles result sets from stored procedures the same way. No special array handling, no row count limits.

Compilation and Registration

First compile the SQLRPGLE source:

text

CRTSQLRPGI OBJ(NICKLITTEN/STOREPRCRS)
 SRCSTMF('/home/nicklitten/source/storeprcrs.sqlrpgle')
 COMMIT(*NONE) OBJTYPE(*PGM) DBGVIEW(*SOURCE)
 CLOSQLCSR(*ENDACTGRP)

Then register it as a stored procedure so SQL knows about it:

SQL

CREATE OR REPLACE PROCEDURE NICKLITTEN.STOREPRCRS (
 IN DEPT_CODE CHAR(10)
)
DYNAMIC RESULT SETS 1
LANGUAGE RPGLE
SPECIFIC STOREPRCRS
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME NICKLITTEN.STOREPRCRS
PARAMETER STYLE GENERAL;

The key clause is DYNAMIC RESULT SETS 1. That tells DB2 this procedure can return one result set.

If you are using TOBi or MAKEi the build usually handles both the compile and the CREATE PROCEDURE step for you.

Best Practices This Example Follows

• Declare the cursor before you open it
• Use host variables (colon prefix) for parameters in the WHERE clause
• Always include ORDER BY if the caller cares about sequence
• Call SET RESULT SETS before you return
• Let SQL close the cursor automatically when the procedure ends
• Keep the procedure focused: one clear purpose, one result set

Result Sets vs Output Parameters

When should you reach for result sets instead of output parameters or arrays?

Use result sets when:

• You need more than one row
• The number of rows can vary
• You want standard SQL client compatibility
• You want the database to manage memory and fetching

Stick with output parameters when:

• You are returning a single value or a single row
• The client is another RPG program that prefers simple parameters
• You need to return multiple unrelated scalar values

Most real world reporting and integration work ends up using result sets.

Multiple Result Sets

You are not limited to one. You can declare several cursors, open them all, and return them together:

rpgle

exec sql declare c1 cursor for ...employees...;
exec sql declare c2 cursor for ...department summary...;

exec sql open c1;
exec sql open c2;

exec sql set result sets cursor c1, cursor c2;

The caller then receives two separate result sets in the order you listed them. Very handy for summary plus detail scenarios.

Troubleshooting Tips

No rows coming back? Check that the department code actually exists in the table and that your WHERE clause logic is correct.

Cursor errors? Make sure you declared the cursor before the OPEN, and that the name in SET RESULT SETS matches exactly.

Client cannot see the procedure? Verify the CREATE PROCEDURE ran successfully and that the user has execute authority on it.

PCML not generating? Add pgminfo(*pcml:*module) to your ctl-opt if you need the interface definition for Java or other strongly typed clients.

Advanced Patterns

You can build the SELECT statement dynamically if the filtering is complex:

Prepare the statement, declare the cursor for the prepared statement, open it using the parameter, then set the result set. That gives you maximum flexibility while still returning a clean result set to the caller.

You can also decide at runtime which cursor to open and return based on an input flag. One procedure, multiple possible result set shapes.

Quick Reference

Declare cursor → Open cursor → Set result sets cursor xxx → Return

DYNAMIC RESULT SETS n in the CREATE PROCEDURE

Host variables with colon in the cursor SELECT

Cursor stays open when procedure ends

Works with every SQL client that supports result sets

Summary

STOREPRCRS is the textbook example of an IBM i SQL stored procedure that returns a dynamic result set. Once you have this pattern in your toolkit you can build clean, client-friendly interfaces for reports, web services, dashboards and integrations without fighting array sizes or row limits.

The beauty is in the simplicity: declare what you want to return, open the cursor, tell SQL you are returning it, and get out of the way. The database and the client do the rest.

Drop this into your own library, change the table and columns to match your data, register it, and call it from your favourite language. Then try adding a second result set or making the SELECT dynamic. That is how you turn this lesson into real working code.

Happy coding, and may your result sets always come back in the right order.