Guru: Creating An SQL Stored Procedure That Returns A Result Set
May 12, 2025 Mike Larsen
Continuing to build out the stored procedure series, I’m going to show how to create an SQL stored procedure that returns a result set. This is very similar to the previous article where I showed how to do this using RPG. In this example, we’re just using pure SQL to achieve the same results.
I created an SQL script (Figure 1) that selects all rows from the Departments table. The SQL statement is the same as the one I used in the RPG program from the prior article, but I’ll describe it again here. 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 script is available to download.
Figure 1. SQL script
On line 12, I direct the script to be either created if it’s not yet created, or to replace it if it already exists.
On lines 13 and 14, I indicate that this stored procedure is using SQL and it returns one result set.
This story contains code, which you can download here.
Line 18 declares a cursor and indicates that I’m returning data to the client, or consumer. A cursor provides a way to access a result table. Lines 20 – 27 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 29, I open the cursor so it can be used to process rows from its result table.
As I did in the prior article, I run this script in VS Code Db2 for i. (Figure 2).
Figure 2. Call the stored procedure from VS Code
The call statement is the same as before. I’m not passing any parameters to the stored procedure, so there is nothing inside the parentheses. When I execute the script, the results are shown in Db2 for i (Figure 3).
Figure 3. Stored procedure result
The results are the same as the result set from the RPG stored procedure, as expected. Why did I show both? My intent was to show multiple ways of achieving the same goal as you may be more comfortable one way or the other.
This concludes the stored procedure series. I have added these tools to my arsenal. Hopefully they can help you, too.
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 That Returns A Result Set
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
