What is a stored procedure in DB2?

A DB2 stored procedure is a named, precompiled set of SQL and procedural logic stored in the database. It can accept input parameters, perform conditional logic and loops, execute DML statements, and return output parameters or result sets. Stored procedures reduce network round trips, enforce business logic at the database layer, and are reusable across applications.

What is the difference between an SQL procedure and an external procedure in DB2?

An SQL procedure is written entirely in SQL PL (DB2's procedural language extension) and stored in the database catalog. An external procedure is written in a host language like Java, C, or COBOL and stored as a compiled binary that DB2 calls at runtime. SQL procedures are easier to create and maintain; external procedures are used when you need capabilities beyond SQL PL, such as file I/O or complex algorithms.

How do I pass parameters to a DB2 stored procedure?

DB2 stored procedures support three parameter modes: IN (input only — the procedure reads but cannot modify it), OUT (output only — the procedure sets a value the caller receives back), and INOUT (both — the caller passes a value that the procedure can read and overwrite). Parameters are declared in the procedure signature: CREATE PROCEDURE myproc (IN p_dept CHAR(3), OUT p_count INT).

How do I handle errors in a DB2 stored procedure?

Use DECLARE HANDLER statements. DECLARE CONTINUE HANDLER catches the error and continues execution; DECLARE EXIT HANDLER catches the error, runs the handler code, and exits the current compound statement. You can handle specific SQLSTATEs (e.g., '23505' for duplicate key) or conditions like SQLEXCEPTION (any error) or SQLWARNING. The GET DIAGNOSTICS statement retrieves the error message text.

DB2 Stored Procedures: Complete Guide with Examples

Stored procedures are one of the most powerful features in IBM DB2. They let you encapsulate complex business logic directly in the database — logic that would otherwise live scattered across application code, running over multiple network trips. A well-designed stored procedure runs entirely within the DB2 engine, which is dramatically faster than issuing the same SQL statements one at a time from a remote application.

This guide covers everything from creating your first simple procedure to advanced topics including parameter modes, conditional branching, loops, cursors, and robust error handling.

What Are Stored Procedures?

A stored procedure is a named routine stored in the DB2 catalog that can be called by name. When called, it executes its body — which can include SQL statements, control flow logic, variable declarations, and error handlers — and optionally returns results to the caller.

Key advantages:

Reduced network traffic: A single CALL replaces dozens of individual SQL statements
Centralised logic: Business rules are enforced at the database layer, regardless of which application calls them
Security: Grant EXECUTE privilege on the procedure without exposing the underlying tables
Reuse: Multiple applications share the same compiled logic

CREATE PROCEDURE — Basic Syntax

sql

CREATE OR REPLACE PROCEDURE procedure_name (
    IN  param1  datatype,
    OUT param2  datatype,
    INOUT param3 datatype
)
LANGUAGE SQL
BEGIN
    -- procedure body
END@

The @ at the end is the statement terminator when running procedures in the DB2 command line processor, because the body itself contains semicolons. In IBM Data Studio you can use the default terminator.

Your First Stored Procedure

sql

CREATE OR REPLACE PROCEDURE GET_EMP_COUNT (
    IN  p_dept    CHAR(3),
    OUT p_count   INTEGER
)
LANGUAGE SQL
BEGIN
    SELECT COUNT(*)
    INTO   p_count
    FROM   EMPLOYEE
    WHERE  WORKDEPT = p_dept;
END@

Call it and retrieve the output:

sql

CALL GET_EMP_COUNT('A00', ?);

The ? is a placeholder for the OUT parameter. In a client application you bind a host variable to it and read the value after the call.

Parameter Modes

DB2 stored procedures support three parameter modes:

Mode	Direction	Description
IN	Caller → Procedure	Read-only input value
OUT	Procedure → Caller	Value set by the procedure
INOUT	Both directions	Caller sends a value; procedure can read and overwrite it

sql

CREATE OR REPLACE PROCEDURE ADJUST_SALARY (
    IN    p_empno    CHAR(6),
    IN    p_pct      DECIMAL(5,2),
    INOUT p_old_sal  DECIMAL(9,2),
    OUT   p_new_sal  DECIMAL(9,2)
)
LANGUAGE SQL
BEGIN
    SELECT SALARY INTO p_old_sal
    FROM   EMPLOYEE WHERE EMPNO = p_empno;

    SET p_new_sal = p_old_sal * (1 + p_pct / 100);

    UPDATE EMPLOYEE
    SET    SALARY = p_new_sal
    WHERE  EMPNO  = p_empno;
END@

Variables and SET

Declare local variables inside the procedure body with DECLARE:

sql

CREATE OR REPLACE PROCEDURE DEPT_SUMMARY (
    IN  p_dept     CHAR(3)
)
LANGUAGE SQL
BEGIN
    DECLARE v_count   INTEGER DEFAULT 0;
    DECLARE v_total   DECIMAL(12,2) DEFAULT 0;
    DECLARE v_avg     DECIMAL(9,2);

    SELECT COUNT(*), SUM(SALARY), AVG(SALARY)
    INTO   v_count,  v_total,     v_avg
    FROM   EMPLOYEE
    WHERE  WORKDEPT = p_dept;

    -- Display results (useful for testing)
    CALL DBMS_OUTPUT.PUT_LINE('Department : ' || p_dept);
    CALL DBMS_OUTPUT.PUT_LINE('Headcount  : ' || CHAR(v_count));
    CALL DBMS_OUTPUT.PUT_LINE('Total pay  : ' || CHAR(v_total));
    CALL DBMS_OUTPUT.PUT_LINE('Average    : ' || CHAR(v_avg));
END@

Variables can also be set with SET statement:

sql

SET v_count = v_count + 1;
SET v_avg   = v_total / v_count;

Conditional Logic — IF / CASE

IF / ELSEIF / ELSE

sql

CREATE OR REPLACE PROCEDURE GRADE_EMPLOYEE (
    IN  p_empno   CHAR(6),
    OUT p_grade   VARCHAR(10)
)
LANGUAGE SQL
BEGIN
    DECLARE v_sal  DECIMAL(9,2);

    SELECT SALARY INTO v_sal
    FROM   EMPLOYEE WHERE EMPNO = p_empno;

    IF v_sal >= 90000 THEN
        SET p_grade = 'Principal';
    ELSEIF v_sal >= 70000 THEN
        SET p_grade = 'Senior';
    ELSEIF v_sal >= 50000 THEN
        SET p_grade = 'Mid';
    ELSE
        SET p_grade = 'Junior';
    END IF;
END@

CASE Statement in a Procedure

sql

CASE v_job
    WHEN 'MANAGER'  THEN SET v_bonus_pct = 15;
    WHEN 'ANALYST'  THEN SET v_bonus_pct = 10;
    WHEN 'DESIGNER' THEN SET v_bonus_pct = 8;
    ELSE                 SET v_bonus_pct = 5;
END CASE;

Loops

WHILE Loop

sql

DECLARE v_i INTEGER DEFAULT 1;

WHILE v_i <= 10 DO
    -- body executes while condition is true
    SET v_i = v_i + 1;
END WHILE;

FOR Loop — Iterating a Result Set

FOR is the cleanest way to iterate over a query result inside a procedure:

sql

CREATE OR REPLACE PROCEDURE GIVE_DEPT_RAISE (
    IN p_dept  CHAR(3),
    IN p_pct   DECIMAL(5,2)
)
LANGUAGE SQL
BEGIN
    FOR emp_row AS
        SELECT EMPNO, SALARY
        FROM   EMPLOYEE
        WHERE  WORKDEPT = p_dept
    DO
        UPDATE EMPLOYEE
        SET    SALARY = emp_row.SALARY * (1 + p_pct / 100)
        WHERE  EMPNO  = emp_row.EMPNO;
    END FOR;
END@

REPEAT Loop

sql

REPEAT
    SET v_i = v_i + 1;
UNTIL v_i > 10
END REPEAT;

Cursors in Stored Procedures

When you need more control over a result set than FOR provides — for example, to handle multiple exceptions row by row — use an explicit cursor:

sql

CREATE OR REPLACE PROCEDURE PROCESS_HIGH_EARNERS ()
LANGUAGE SQL
BEGIN
    DECLARE v_empno    CHAR(6);
    DECLARE v_salary   DECIMAL(9,2);
    DECLARE v_done     INTEGER DEFAULT 0;

    DECLARE c_emp CURSOR FOR
        SELECT EMPNO, SALARY
        FROM   EMPLOYEE
        WHERE  SALARY > 80000
        ORDER BY SALARY DESC;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET v_done = 1;

    OPEN c_emp;

    FETCH_LOOP: LOOP
        FETCH c_emp INTO v_empno, v_salary;
        IF v_done = 1 THEN LEAVE FETCH_LOOP; END IF;

        -- Process each row here
        INSERT INTO HIGH_EARNER_LOG (EMPNO, SALARY, LOG_DATE)
        VALUES (v_empno, v_salary, CURRENT DATE);
    END LOOP FETCH_LOOP;

    CLOSE c_emp;
END@

Error Handling

DB2 stored procedures use DECLARE HANDLER to catch errors and warnings.

Handler Types

CONTINUE handler: catches the condition and resumes execution after the failing statement.

EXIT handler: catches the condition and exits the compound statement (BEGIN/END block) where the handler is declared.

Handling Specific SQLSTATEs

sql

CREATE OR REPLACE PROCEDURE SAFE_INSERT (
    IN  p_empno   CHAR(6),
    IN  p_dept    CHAR(3),
    OUT p_status  VARCHAR(50)
)
LANGUAGE SQL
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'  -- Duplicate key
        SET p_status = 'ERROR: Duplicate employee number';

    DECLARE CONTINUE HANDLER FOR SQLSTATE '23503'  -- FK violation
        SET p_status = 'ERROR: Department does not exist';

    SET p_status = 'SUCCESS';

    INSERT INTO EMPLOYEE (EMPNO, WORKDEPT, EDLEVEL, FIRSTNME, LASTNAME, MIDINIT)
    VALUES (p_empno, p_dept, 14, 'NEW', 'EMPLOYEE', 'X');
END@

Catching All SQL Errors

sql

DECLARE v_sqlstate  CHAR(5);
DECLARE v_msg       VARCHAR(200);

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1
        v_sqlstate = RETURNED_SQLSTATE,
        v_msg      = MESSAGE_TEXT;
    -- Log the error or set an output parameter
    SET p_error_msg = 'SQLSTATE=' || v_sqlstate || ': ' || v_msg;
END;

Returning Result Sets

A procedure can return one or more result sets to the calling application by declaring a cursor WITH RETURN:

sql

CREATE OR REPLACE PROCEDURE GET_DEPT_EMPLOYEES (
    IN p_dept CHAR(3)
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE c_result CURSOR WITH RETURN FOR
        SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
        FROM   EMPLOYEE
        WHERE  WORKDEPT = p_dept
        ORDER BY SALARY DESC;

    OPEN c_result;
    -- Do NOT close — DB2 returns the open cursor to the caller
END@

The caller (Java, COBOL, Python via ibm_db, etc.) fetches rows from the returned cursor just like a regular result set.

Calling Stored Procedures

From DB2 CLI / command line:

sql

CALL GET_EMP_COUNT('A00', ?);
CALL GIVE_DEPT_RAISE('B01', 5.0);

From COBOL:

cobol

EXEC SQL
    CALL GET_EMP_COUNT(:WS-DEPT, :WS-COUNT)
END-EXEC.

From Java (JDBC):

java

CallableStatement cs = conn.prepareCall("{CALL GET_EMP_COUNT(?, ?)}");
cs.setString(1, "A00");
cs.registerOutParameter(2, Types.INTEGER);
cs.execute();
int count = cs.getInt(2);

Viewing and Managing Procedures

sql

-- List all stored procedures in a schema
SELECT PROCNAME, LANGUAGE, PARM_COUNT, CREATE_TIME
FROM   SYSCAT.PROCEDURES
WHERE  PROCSCHEMA = 'DB2ADMIN'
ORDER BY PROCNAME;

-- View procedure source
SELECT TEXT
FROM   SYSCAT.PROCEDURES
WHERE  PROCNAME = 'GET_EMP_COUNT';

-- Drop a procedure
DROP PROCEDURE GET_EMP_COUNT;

-- Drop with specific signature (when overloaded)
DROP SPECIFIC PROCEDURE GET_EMP_COUNT_CHAR3;

Practical Example: Payroll Processing Procedure

This complete example demonstrates a realistic payroll procedure with error handling:

sql

CREATE OR REPLACE PROCEDURE PROCESS_PAYROLL (
    IN  p_dept      CHAR(3),
    IN  p_raise_pct DECIMAL(5,2),
    OUT p_updated   INTEGER,
    OUT p_status    VARCHAR(100)
)
LANGUAGE SQL
BEGIN
    DECLARE v_err_state CHAR(5);
    DECLARE v_err_msg   VARCHAR(200);

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        GET DIAGNOSTICS CONDITION 1
            v_err_state = RETURNED_SQLSTATE,
            v_err_msg   = MESSAGE_TEXT;
        SET p_status  = 'FAILED: ' || v_err_state || ' - ' || v_err_msg;
        SET p_updated = 0;
    END;

    SET p_updated = 0;
    SET p_status  = 'SUCCESS';

    UPDATE EMPLOYEE
    SET    SALARY = SALARY * (1 + p_raise_pct / 100)
    WHERE  WORKDEPT = p_dept
    AND    SALARY IS NOT NULL;

    GET DIAGNOSTICS p_updated = ROW_COUNT;

    COMMIT;

    SET p_status = 'SUCCESS: ' || CHAR(p_updated) || ' records updated';
END@

Next Steps

Stored procedures work hand-in-hand with triggers for automatic event-driven logic. Read our DB2 Triggers guide to see how to enforce rules automatically on INSERT, UPDATE, and DELETE. For a complete reference of DB2 SQL commands, see the DB2 Cheat Sheet. Follow the full learning path at the DB2 Mastery course hub.

DB2 Stored Procedures: CREATE, CALL, Parameters and Error Handling