IBM i SQL Stored Procedures

What Are SQL Stored Procedures?

A stored procedure is a compiled SQL program that performs a specific task like updating a table, retrieving data, or executing business logic. Think of it as a reusable function that lives in the database.

Why Use Them?

  • Encapsulation of logic

  • Performance boost (precompiled execution)

  • Easier maintenance and modularity

  • Security and access control

Creating Your First SQL Stored Procedure

Let’s create a simple procedure that updates a customer’s status.

CREATE OR REPLACE PROCEDURE UPDATE_CUSTOMER_STATUS (
IN CUST_ID INT,
IN NEW_STATUS CHAR(1)
)
LANGUAGE SQL
BEGIN
UPDATE CUSTOMER
SET STATUS = NEW_STATUS
WHERE ID = CUST_ID;
END;

Explanation:

  • IN parameters are inputs.

  • LANGUAGE SQL tells IBM i this is a native SQL procedure.

  • BEGIN...END wraps the logic.

Calling the Procedure

You can call it from STRSQL, ACS Run SQL Scripts, or embedded in RPGLE:

CALL UPDATE_CUSTOMER_STATUS(1001, 'A');

Or from SQLRPGLE:

SQL
CALL UPDATE_CUSTOMER_STATUS(:CustomerID :NewStatus);

Adding Output Parameters

Let’s return a count of updated rows:

CREATE OR REPLACE PROCEDURE UPDATE_CUSTOMER_STATUS (
IN CUST_ID INT,
IN NEW_STATUS CHAR(1),
OUT ROWS_UPDATED INT
)
LANGUAGE SQL
BEGIN
UPDATE CUSTOMER
SET STATUS = NEW_STATUS
WHERE ID = CUST_ID;

SET ROWS_UPDATED = ROW_COUNT;
END;

Error Handling with DECLARE CONTINUE HANDLER

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET ROWS_UPDATED = -1;

This ensures your procedure doesn’t crash and burns gracefully.

Best Practices

Use meaningful procedure names (UPDATE_CUSTOMER_STATUS, not PROC1)

Document parameters and expected behavior

Keep procedures focused - one task per procedure

Use output parameters for feedback

Wrap logic in transactions if needed (COMMIT, ROLLBACK)

Done?
 Mark lesson complete
Previous Lesson
Next Lesson
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>