Create an IBM i SQLRPGLE Stored Procedure

How I Create an IBM i QSLRPGLE Stored Procedure

Welcome to a quick lesson on building an IBM i RPGLE stored procedure. I use these to mix RPG code with SQL calls. External programs access my RPG logic via SQL. I apply them for tasks like database inserts or queries. Think of it as bridging old-school RPG with modern SQL needs.

Objective: By the end, you should be able to build, compile, register, and call a basic procedure. 

Step 1: Plan Your Procedure

Let's start by outlining inputs, outputs, and result sets.

For our example, we take an employee number as input and return a name and a city. If the employee hides, we handle that gracefully.

We are going to look at two different code examples (1) written in SQLRPGLE and accessing our file using SQL and (2) written in native RPGLE using native DB2 File IO.

Let's look at the SQL code example first

Step 2: Write the SQLRPGLE Code

This SQLRPGLE program is a clean, modular example of an IBM i stored procedure written in free-format RPG that retrieves employee details based on a given employee ID.

Program Header

• main(mainline): Sets the entry point to the mainline procedure
• optimize(*full): Enables full optimization during compile
• option(...): Controls compile-time options—no debug I/O, source statement tracking, and no unused reference warnings
• pgminfo(*pcml:*module): Generates PCML metadata for external calls. (not necessary for this example but something I add as standard)
• actgrp(*new): Runs in a new activation group (good practice for stored procedures)
• indent('| '): Sets indentation style for generated source
• alwnull(*usrctl): allow the incoming return values to be *NULL
• copyright(...): Adds a custom copyright string.

Procedure Declaration

• Dcl-Pi: Declares the procedure interface
• emp_id_in: Input parameter (employee ID)
• emp_name_out, city_out: Output parameters to return employee details
• const: Ensures emp_id_in is read-only.

SQL Logic

• Executes a static SQL SELECT to fetch emp_name and city from the employee_master table
• Uses host variables (: prefix) to bind RPG variables to SQL.

Error Handling

• Checks the SQL return code (sqlcode)
• If no match is found (i.e., sqlcode ≠ 0), sets default values.

Return

Simply, ends the procedure and returns control to the caller.

Step 3: Compile the Code

We should compile this - Run CRTSQLRPGI 

If errors pop up, we fix them before blaming the copy/paste, me or your keyboard. 

So, now we have a program that can be called from anywhere. It will consume that single parameter of employee id number and return the name and city of the employee, if found. The obvious question is - how do we register this program as a stored procedure?

Step 4: Register in SQL

Now we need to use SQL, and the CREATE PROCEDURE statement to add our stored procedure and to make it official.

NOTE I am qualifying the schema name (NICKLITTEN) against the long STORED PROCEDURE NAME (GET_EMPLOYEES_SQL) and the RPGLE Program name (STOREPRCS):

Step 5: Call the Procedure

From SQL, we run CALL NICKLITTEN.GET_EMPLOYEES_SQL ( 12345 , ? , ? ) 

The ? grabs the output like a catcher’s mitt.

Let's test this using a similar script, we generated for the testing lesson previously. This is a little more friendly - if you want to test with different employee number simply change the value of the V_EMP variable.

Remember to use our new procedure name: GET_EMPLOYEES_SQL

If I paste this into my IBM i ACS screen, I see this:

Excellent!

In the next lesson, we will create a native DB2 IO RPGLE program to perform the exact same function...