Guru: Creating An RPG Stored Procedure With Parameters

January 20, 2025 Mike Larsen

There are times we need to build processes that will be used across different platforms. An example is a pricing routine. When a system needs to get pricing, we need to make sure the price the customer pays is the same regardless of how the order was created in our system.

One way to create this functionality is using web services. Another way we can provide this functionality is through stored procedures. This series of tips will focus on different ways we can build stored procedures on IBM i.

For the first part, I created an RPG program (Figure 1) that accepts an input parameter and sends an output parameter back. The program is very simple, but illustrates what we’re looking to achieve. The source code for the program is available to download.

Figure 1. RPG program

The program receives a message as input, then creates an output message to return to the consumer. It’s not very exciting, but it satisfies the requirement. Next, I build an SQL script to catalog the RPG program as a stored procedure (Figure 2).

Figure 2. Catalog the RPG program as a stored procedure

Let’s step through this script. 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 MLRSTRPRC1.

Lines 19 and 20 define the input and output parameters for the stored procedure. They are defined the same as I defined them in the RPG program.

Line 23 indicates the stored procedure will be utilizing an RPG program. Not Deterministic means the procedure might not return the same result each time it is called.

Line 24 points to the location of the RPG program that will be invoked in the procedure.

Finally, on line 25, Parameter Style General indicates the parameters are passed to an external program.

In the comments section of the program (line 8), I’ve included the statement I used to create the stored procedure.

Once the script is created, I can test it by running it in ACS. In Run SQL Scripts, call the procedure (Figure 3).

Figure 3. Call the stored procedure from ACS

I’m passing a parameter Hello as input to the script. Since the second parameter is an output parameter being passed back to me, I use a placeholder (question mark) indicating so. When I run the script, ACS displays the result in the messages view (Figure 4).

Figure 4. Stored procedure result

This is the first part of a series that will show different aspects of using stored procedures. In the next part, I’ll show how to create a stored procedure that uses RPG to return a result set back to the consumer.

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 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

Guru: Creating PDF Documents With Python

Guru: Creating Excel Spreadsheets With Python