Create a Basic IBM i SQL Stored Procedure
Want to reuse SQL queries? Learn how to create stored procedures in IBM i. Save time. Run CALL procedure_name instead of rewriting your query.
Welcome Intrepid IBM i Developer - You've come to the right place to learn how to write and run a simple SQL stored procedure on IBM i. This lesson walks you through the syntax, structure, and execution steps using real examples. You’ll use CREATE PROCEDURE, define parameters, and run your procedure with CALL.
What you’ll do:
- Use
CREATE OR REPLACE PROCEDUREto define a stored procedure - Add input parameters with
INkeyword - Write SQL logic inside
BEGIN ... END - Run your procedure using
CALL procedure_name - Test results using sample data from a table
What you’ll need:
- Access to IBM i system with SQL enabled
- A sample table like EMPLOYEE or CUSTOMER
- Basic knowledge of SQL SELECT and UPDATE statements
By the end of this lesson, you’ll be able to:
- Create a stored procedure that returns filtered data
- Pass parameters to control query results
- Reuse procedures in your programs or scripts
First, let's look at the details. Then you can follow me as I video a hands on example of write one and calling it from SQL.
Sample EMPLOYEE Table for our examples
First things first, let's make sure you have a working library for these code samples. In my code snippets, I am putting everything in a library called NICKLITTEN - feel free to change the code samples to the library of your choice. Or simply create a working library like this:
Now, before we do anything lets create a simple example file to hold the data we will be accessing.
For this lesson, I've chosen an example 'employee' database:
EMP_ID integer generated always as identity (
start with 1 increment by 1 ),
EMP_NAME varchar(50) not null,
JOB_TITLE varchar(30),
DEPT_ID integer,
CITY varchar(30),
HIRE_DATE date,
SALARY decimal(10, 2),
ACTIVE char(1) default 'Y',
primary key (EMP_ID)
);
Field Breakdown
EMP_ID: Auto-generated unique ID for each employee.EMP_NAME: Full name, required.JOB_TITLE: Job role or title.DEPT_ID: Department number.CITY: Work location.HIRE_DATE: Date of joining.SALARY: Monthly salary, up to 99999999.99.ACTIVE: 'Y' or 'N' to mark active status.
Run this in ACS Run SQL Scripts or any SQL interface on IBM i.
Here’s an SQL INSERT script to generate 5 employee rows for your EMPLOYEE_MASTER table. This version uses hardcoded sample data to simulate randomness. You can tweak values or wrap it in a loop if using a scripting tool.
EMP_NAME,
JOB_TITLE,
DEPT_ID,
CITY,
HIRE_DATE,
SALARY,
ACTIVE
)
values
(
'Nick Litten',
'Analyst',
101,
'Spain',
date('2021-06-15'),
6200.00,
'Y'
),
(
'Bruce Lee',
'Developer',
102,
'Hong Kong',
date('2022-03-22'),
7100.00,
'Y'
),
(
'Toby Schofield',
'Manager',
103,
'Cornwall',
date('2020-11-01'),
8800.00,
'Y'
),
(
'Captain Kirk',
'Support',
104,
'Enterprise',
date('2023-01-10'),
5400.00,
'Y'
),
(
'Don Dong Dong',
'Designer',
105,
'Denia',
date('2021-09-30'),
6700.00,
'Y'
);
OK, so now we have a table ready to be read by our SQL Stored Proc so let's look at it two ways.
Basic Stored Procedure
To create an SQL stored procedure on IBM i, use the CREATE PROCEDURE statement inside a SQL script or through a tool like ACS Run SQL Scripts.
Here's a simple example that says "use employee NUMBER to return the employee's NAME and CITY":
in EMP_ID_IN int,
out EMP_NAME_OUT varchar(50),
out CITY_OUT varchar(30)
)
language SQL
begin
select EMP_NAME,
CITY
into EMP_NAME_OUT,
CITY_OUT
from NICKLITTEN.EMPLOYEE_MASTER
where EMP_ID = EMP_ID_IN;
end;
So - now you have created your first ever stored procedure!
Next step is to test it...