Stored Procedures

Difficulty: Advanced · ~12 min read

Overview

A stored procedure is a named block of SQL + procedural code that lives inside the database. You call it like a function:

CALL transfer_funds(101, 102, 50.00);

Procedures are great for:

Encapsulating business rules — one place to maintain a multi-statement workflow
Reducing round trips — multiple statements run server-side in one call
Security boundaries — grant EXECUTE on a procedure without granting direct table access
Reusable utility logic — a cleanup_old_data procedure callable from anywhere

DB2 procedures are written in SQL PL (SQL Procedural Language), which adds variables, control flow, exception handlers, and cursors to standard SQL. You can also write procedures in Java or C, but SQL PL is by far the most common.

Syntax

CREATE [OR REPLACE] PROCEDURE proc_name (
  IN  param1 TYPE,
  OUT param2 TYPE,
  INOUT param3 TYPE
)
LANGUAGE SQL
BEGIN
  -- Declarations (must come first)
  DECLARE local_var TYPE [DEFAULT value];
  DECLARE c CURSOR FOR SELECT ...;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN ... END;

  -- Statements
  SET local_var = 0;
  IF condition THEN ...
  ELSE ...
  END IF;

  -- Loops
  WHILE condition DO ... END WHILE;
  FOR row AS SELECT ... DO ... END FOR;

  -- Result sets (returned to caller)
  OPEN c;
END@

Important: SQL PL bodies contain ; internally, so they conflict with the DB2 CLP's default ; terminator. Use -td@ (or another character) when running scripts:

db2 -td@ -vf my_procs.sql

Examples

Example 1: A minimal procedure

CREATE OR REPLACE PROCEDURE hello (IN p_name VARCHAR(80), OUT p_greeting VARCHAR(200))
LANGUAGE SQL
BEGIN
  SET p_greeting = 'Hello, ' || p_name || '!';
END@

CALL hello('DB2', ?)@

Output:

  Value of output parameters
  --------------------------
  Parameter Name  : P_GREETING
  Parameter Value : Hello, DB2!

The ? placeholder is how the CLP receives OUT parameters.

Example 2: Procedure with logic + transaction

CREATE OR REPLACE PROCEDURE transfer_funds (
  IN  p_from_id  INTEGER,
  IN  p_to_id    INTEGER,
  IN  p_amount   DECIMAL(11,2)
)
LANGUAGE SQL
BEGIN
  DECLARE v_balance DECIMAL(11,2);

  -- Check source account has enough funds
  SELECT balance INTO v_balance
    FROM accounts
    WHERE account_id = p_from_id;

  IF v_balance < p_amount THEN
    SIGNAL SQLSTATE '70001'
      SET MESSAGE_TEXT = 'Insufficient funds';
  END IF;

  -- Two updates in one transaction
  UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from_id;
  UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to_id;

  INSERT INTO transfer_log (from_id, to_id, amount, transferred_at)
  VALUES (p_from_id, p_to_id, p_amount, CURRENT_TIMESTAMP);
END@

CALL transfer_funds(101, 102, 50.00)@

SIGNAL SQLSTATE raises a custom error — the caller sees a non-zero SQLSTATE and the transaction is rolled back automatically.

Example 3: Control flow — IF / ELSE / CASE

CREATE OR REPLACE PROCEDURE classify_balance (
  IN  p_account_id INTEGER,
  OUT p_tier       VARCHAR(20)
)
LANGUAGE SQL
BEGIN
  DECLARE v_balance DECIMAL(11,2);
  SELECT balance INTO v_balance FROM accounts WHERE account_id = p_account_id;

  IF v_balance IS NULL THEN
    SET p_tier = 'UNKNOWN';
  ELSEIF v_balance < 1000 THEN
    SET p_tier = 'BRONZE';
  ELSEIF v_balance < 10000 THEN
    SET p_tier = 'SILVER';
  ELSE
    SET p_tier = 'GOLD';
  END IF;
END@

DB2 also supports a CASE form for cleaner multi-branch logic:

CASE
  WHEN v_balance IS NULL    THEN SET p_tier = 'UNKNOWN';
  WHEN v_balance < 1000     THEN SET p_tier = 'BRONZE';
  WHEN v_balance < 10000    THEN SET p_tier = 'SILVER';
  ELSE                           SET p_tier = 'GOLD';
END CASE;

Example 4: Loops with FOR

CREATE OR REPLACE PROCEDURE summarize_regions ()
LANGUAGE SQL
BEGIN
  DECLARE v_total DECIMAL(15,2) DEFAULT 0;

  FOR rec AS
    SELECT region, total_sales FROM v_region_sales
  DO
    SET v_total = v_total + rec.total_sales;
  END FOR;

  INSERT INTO daily_summary (run_date, grand_total)
  VALUES (CURRENT_DATE, v_total);
END@

FOR ... DO ... END FOR is the cleanest way to iterate a result set — no explicit cursor declare/open/fetch/close required.

Example 5: Returning a result set

CREATE OR REPLACE PROCEDURE top_customers (IN p_n INTEGER)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
  DECLARE c CURSOR WITH RETURN FOR
    SELECT customer_id, name, total_spent
    FROM   v_customer_totals
    ORDER  BY total_spent DESC
    FETCH FIRST p_n ROWS ONLY;
  OPEN c;
END@

CALL top_customers(10)@

DYNAMIC RESULT SETS n declares the number of cursors the procedure returns. WITH RETURN marks a cursor as one that flows back to the caller.

Example 6: Exception handlers

CREATE OR REPLACE PROCEDURE safe_insert_customer (
  IN  p_email VARCHAR(254),
  OUT p_status VARCHAR(40)
)
LANGUAGE SQL
BEGIN
  DECLARE EXIT HANDLER FOR SQLSTATE '23505'  -- unique violation
    SET p_status = 'DUPLICATE_EMAIL';

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SET p_status = 'OTHER_ERROR';

  INSERT INTO customers (email, signup_date)
  VALUES (p_email, CURRENT_DATE);

  SET p_status = 'OK';
END@

Handler type	Behaviour after handler runs
`EXIT`	Procedure ends
`CONTINUE`	Execution resumes after the failing statement
`UNDO`	Rolls back to start of atomic block, then exits

Example 7: Dropping a procedure

DROP PROCEDURE hello (VARCHAR(80), VARCHAR(200))@
-- Or, if signature is unique:
DROP PROCEDURE hello@

The signature must match if multiple procedures share the same name.

Notes & Tips

Always change the statement terminator when running SQL PL scripts: db2 -td@ -vf script.sql. Otherwise DB2 cuts off your procedure at the first internal ;.
DB2 stores procedure source in SYSCAT.PROCEDURES (column text). View definitions with db2look -d <db> -e -z SCHEMA -t -td@.
Errors with SIGNAL SQLSTATE '<5-char>' — values starting with 7–9 or Z are user-defined per the SQL standard.
Procedures run in the caller's transaction by default. They can COMMIT/ROLLBACK but only with AUTONOMOUS (DB2 11+) or by being called from an autonomous context.
For complex error handling, use GET DIAGNOSTICS inside a handler to read RETURNED_SQLSTATE, ROW_COUNT, etc.
Parameter modes: IN is read-only inside, OUT is written back to caller, INOUT is both.
DB2 caches compiled procedure plans — first call is slower (compile), subsequent calls reuse the plan.

Practice Exercises

Write a procedure archive_old_orders(IN p_days INTEGER) that copies orders older than p_days to orders_archive and deletes them from orders. Wrap it so a failure rolls everything back.
Add an exception handler that catches SQLSTATE '02000' (no data found) and returns a custom message.
Build a procedure that returns the top 5 customers as a result set (use DYNAMIC RESULT SETS 1).
Use GET DIAGNOSTICS to capture and return the number of rows affected by an UPDATE.
Write a procedure that uses a FOR loop to recalculate every customer's loyalty tier based on their total_spent.

Quick Quiz

Q1. Why do you need to change the DB2 CLP terminator to @ (or similar) when creating procedures?

Show answer

The default terminator is ;. SQL PL procedure bodies contain ; internally — between statements, after END IF, etc. Without changing the terminator, the CLP would cut your procedure off at the first internal ;, producing a syntax error. Use db2 -td@ to set a different terminator just for that script.

Q2. What's the difference between an EXIT handler and a CONTINUE handler?

Show answer

After an EXIT handler runs, the procedure (or compound block) terminates. After a CONTINUE handler runs, execution resumes at the statement after the one that failed. Use EXIT for unrecoverable errors; use CONTINUE to log-and-keep-going style processing.

Q3. How do you return a result set from a DB2 procedure?

Show answer

Declare the procedure with DYNAMIC RESULT SETS n (where n is the count of cursors to return), declare each cursor with WITH RETURN, then OPEN the cursor and don't close it. The open cursor flows back to the caller.

Next Up

Procedures are explicit — you CALL them. Next we look at triggers, which fire automatically on data changes.