Guru: Creating An RPG Stored Procedure That Returns A Result Set
March 17, 2025 Mike Larsen
In the previous article, I showed how to create an RPG stored procedure that uses parameters. In this article, I’m going to show how to create an RPG stored procedure that returns a result set, as we would likely encounter this scenario more frequently. The data used in this example comes from the Department table from IBM’s Sample database.
I created an RPG program (Figure 1) that selects all rows from the Departments table. I didn’t add any selection criteria as I know there aren’t many rows in the table, but you may want to limit the number of rows selected when implementing a production program. The source code for the program is available to download.
Figure 1. RPG program
I start by declaring a cursor and indicating that I’m returning data to the client, or consumer on line 33. A cursor provides a way to access a result table. Lines 35 – 40 select columns from the Department table. Since some of the columns are nullable, I added some code to show N/A if the column is null. On line 43, I open the cursor and on line 46, I associate the cursor with the result set. That’s it for the RPG program.
Next, I created an SQL script to catalog the RPG program as a stored procedure (Figure 2).
Figure 2. Catalog the RPG program as a stored procedure
On line 17, I’m telling the system that I’d like to create or replace a stored procedure that resides in library MLLIB. The stored procedure name is MLRSTRPROC.
In the prior article, lines 18 and 19 were used to define the input and output parameters for the stored procedure. Since this procedure doesn’t have any parameters, I left these lines blank.
Since I’m returning a result set in this procedure, I indicate that on line 21. There is only one result set being returned by this procedure, but you can return multiple result sets if you choose.
Line 22 indicates the stored procedure will be utilizing an RPG program.
Modifies SQL Data on line 23 indicates that the procedure can execute any SQL statement except statements that are not supported in procedures.
Line 24 points to the location of the RPG program that will be invoked in the procedure
In the comments section of the program (line 8), I’ve included the statement I used to create the stored procedure.
I’m going to run this script in VS Code, but you could also run it in ACS. (Figure 3).
Figure 3. Call the stored procedure from VS Code
I’m using Db2 for i to execute the procedure. Since I’m not passing any parameters, there is nothing inside the parentheses. When I execute the script, the results are shown in Db2 for i (Figure 4).
Figure 4. Stored procedure result
With a small amount of code, I’ve built a useful tool that can be consumed from multiple platforms. In the next part, I’m going create a stored procedure that returns a result set just using SQL.
Mike Larsen is a director of information technology at Auburn Pharmaceutical and has been working with IBM i systems for over 20 years. He specializes in RPG, CL, and SQL and recently has been working with PHP and Python. Current projects have given Mike the opportunity to work with generating and parsing XML and JSON from SQL and consuming SOAP and REST web services. Although his main area of expertise is on IBM i, Mike has a passion for learning other languages and how he can integrate other platforms with IBM i.
RELATED STORIES
Guru: Creating An RPG Stored Procedure With Parameters
Guru: Creating A Web Service With Basic Authentication
Guru: Parsing JSON That Has Spaces In The Key
Guru: Partitioning Result Sets Using SQL
Guru: Comparing IFS Directories Using SQL
Guru: String Manipulation Using SQL
Guru: Regular Expressions, Part 1
Guru: Regular Expressions, Part 2
Guru: Debugging SQL Stored Procedures With ACS
