Stored Procedures and Functions, Parameters, Handlers, Cursors, and Diagnostics

Learning outcomes

ServiceHub must close a work order, record who closed it, and reject an already-closed order as one atomic operation. If every application reimplements those steps, transaction and error behavior can diverge. MariaDB stored routines are named server-side programs. A stored procedure is invoked with CALL and is well suited to explicit workflows, OUT parameters and transaction control. A stored function returns a value and can be called inside SQL expressions, which makes its side effects and cost much more sensitive. MariaDB's procedural language follows SQL/Persistent Stored Modules (SQL/PSM)-style constructs and includes MariaDB-specific behavior; do not assume it is identical to another database's procedural language.

1. Recreate the ServiceHub schema and define a routine contract

A procedure should have a narrow business purpose and a testable interface. The first routine accepts a work-order identifier and actor, returns the number of changed rows, and either commits the whole close operation or propagates an error.

DROP DATABASE IF EXISTS servicehub_programmability_lab;CREATE DATABASE servicehub_programmability_lab;USE servicehub_programmability_lab;CREATE TABLE work_orders (  work_order_id BIGINT PRIMARY KEY,  status ENUM('open','assigned','closed','cancelled') NOT NULL,  priority TINYINT NOT NULL,  assigned_team VARCHAR(40) NULL,  opened_at DATETIME NOT NULL,  closed_at DATETIME NULL) ENGINE=InnoDB;CREATE TABLE work_order_history (  history_id BIGINT AUTO_INCREMENT PRIMARY KEY,  work_order_id BIGINT NOT NULL,  action_name VARCHAR(40) NOT NULL,  actor VARCHAR(100) NOT NULL,  action_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  CONSTRAINT fk_hist_wo FOREIGN KEY(work_order_id) REFERENCES work_orders(work_order_id)) ENGINE=InnoDB;INSERT INTO work_orders VALUES(2001,'open',5,'alpha','2026-08-15 08:00:00',NULL),(2002,'assigned',3,'beta','2026-08-16 09:00:00',NULL),(2003,'closed',1,'alpha','2026-08-10 10:00:00','2026-08-11 11:00:00');

Both tables are InnoDB so the update and history insert can participate in the same transaction. If one table used a non-transactional engine, a rollback could not guarantee the same atomicity across both objects.

2. Procedures make workflow and transaction boundaries explicit

Parameters are part of the API. IN values enter the routine, OUT values are written by the routine, and INOUT values do both. Local declarations appear at the beginning of a compound block before executable statements. The handler below is intentionally broad at the outer transaction boundary: any SQL exception records diagnostics locally, rolls back, and then RESIGNALs the original condition so the caller still sees failure.

DELIMITER $$CREATE OR REPLACE PROCEDURE sp_close_work_order(  IN p_work_order_id BIGINT,  IN p_actor VARCHAR(100),  OUT p_rows_changed INT)SQL SECURITY INVOKERMODIFIES SQL DATABEGIN  DECLARE v_status VARCHAR(20);  DECLARE v_sqlstate CHAR(5);  DECLARE v_message TEXT;  DECLARE EXIT HANDLER FOR SQLEXCEPTION  BEGIN    GET DIAGNOSTICS CONDITION 1      v_sqlstate = RETURNED_SQLSTATE,      v_message = MESSAGE_TEXT;    ROLLBACK;    RESIGNAL;  END;  SET p_rows_changed = 0;  START TRANSACTION;  SELECT status INTO v_status  FROM work_orders  WHERE work_order_id = p_work_order_id  FOR UPDATE;  IF v_status IS NULL THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='work order not found';  END IF;  IF v_status='closed' THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='work order already closed';  END IF;  UPDATE work_orders  SET status='closed', closed_at=CURRENT_TIMESTAMP  WHERE work_order_id=p_work_order_id;  SET p_rows_changed = ROW_COUNT();  INSERT INTO work_order_history(work_order_id,action_name,actor)  VALUES(p_work_order_id,'closed',p_actor);  COMMIT;END$$DELIMITER ;

SIGNAL SQLSTATE '45000' creates an application-defined exception. GET DIAGNOSTICS reads the diagnostics area associated with the error. RESIGNAL preserves the failure for the client instead of silently converting it into success.

SET @changed = NULL;CALL sp_close_work_order(2001,'chapter11-lab',@changed);SELECT @changed AS rows_changed;SELECT work_order_id,status,closed_at FROM work_orders WHERE work_order_id=2001;SELECT work_order_id,action_name,actor,action_at FROM work_order_history WHERE work_order_id=2001;SHOW CREATE PROCEDURE sp_close_work_order\GSELECT ROUTINE_NAME,ROUTINE_TYPE,DATA_ACCESS,SECURITY_TYPE,DEFINERFROM information_schema.ROUTINESWHERE ROUTINE_SCHEMA='servicehub_programmability_lab';

The success case should change one work order and add one history row. SHOW CREATE PROCEDURE proves the stored definition MariaDB accepted. INFORMATION_SCHEMA.ROUTINES exposes metadata, but neither proves all transaction paths are correct; the failure tests below provide that evidence.

3. Functions belong in expressions, so keep them predictable

A stored function returns a value and can be invoked once per row in a query. That makes a seemingly small function capable of multiplying CPU or I/O cost across a large result set. Functions also have stricter restrictions than procedures: they cannot return result sets and cannot own arbitrary transaction control such as COMMIT or ROLLBACK. Even where DML is technically allowed, side-effecting functions called from SQL are difficult to reason about and can interact badly with binary logging and replication policy. Prefer deterministic, side-effect-free functions for expression logic.

DELIMITER $$CREATE OR REPLACE FUNCTION fn_priority_band(p_priority TINYINT)RETURNS VARCHAR(12)DETERMINISTICNO SQLSQL SECURITY INVOKERRETURN CASE  WHEN p_priority >= 5 THEN 'critical'  WHEN p_priority >= 3 THEN 'normal'  ELSE 'low'END$$DELIMITER ;SELECT work_order_id,priority,fn_priority_band(priority) AS priority_bandFROM work_ordersORDER BY work_order_id;SHOW CREATE FUNCTION fn_priority_band\G

The DETERMINISTIC and NO SQL characteristics document the routine's contract; they do not automatically prove the body really satisfies that contract. Treat them as code-review assertions and test the function directly.

4. Handlers and diagnostics should narrow failure, not hide it

A DECLARE ... HANDLER tells MariaDB what to do when a condition occurs. EXIT leaves the current compound block after the handler runs; CONTINUE resumes after the statement that raised the condition. Use named conditions or specific SQLSTATE/error classes where the recovery is truly known. A blanket CONTINUE HANDLER FOR SQLEXCEPTION that ignores every failure can commit partial business state.

DELIMITER $$CREATE OR REPLACE PROCEDURE sp_bad_close(IN p_id BIGINT)BEGIN  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;  START TRANSACTION;  UPDATE work_orders SET status='closed',closed_at=CURRENT_TIMESTAMP  WHERE work_order_id=p_id;  -- Deliberately fail: actor is NOT NULL.  INSERT INTO work_order_history(work_order_id,action_name,actor)  VALUES(p_id,'closed',NULL);  COMMIT;END$$DELIMITER ;CALL sp_bad_close(2002);SELECT work_order_id,status FROM work_orders WHERE work_order_id=2002;SELECT * FROM work_order_history WHERE work_order_id=2002;

The dangerous result is a closed work order without its history row. The handler converted a data-integrity failure into apparent success and allowed the later COMMIT. Repair the pattern by using an EXIT handler at the transaction boundary, rolling back, and resignal the original error. If a specific duplicate or “not found” condition is genuinely recoverable, handle only that condition and verify the resulting state explicitly.

SET @changed = NULL;CALL sp_close_work_order(2003,'chapter11-lab',@changed);-- Expected: application SQLSTATE 45000, "work order already closed".SELECT work_order_id,status,closed_at FROM work_orders WHERE work_order_id=2003;SELECT COUNT(*) AS close_history_rowsFROM work_order_historyWHERE work_order_id=2003 AND action_name='closed';

The second call should fail and leave the pre-existing closed order unchanged without adding a new close-history row. That is the observable transaction contract.

5. Cursors are procedural row iteration—use them only when the operation is truly row-oriented

A cursor lets a routine fetch query rows sequentially. MariaDB cursors are read-only and are typically controlled with a NOT FOUND handler. They are useful when each row requires procedural work that cannot be expressed safely as one set-based statement, but they add round-by-round execution inside the server. Do not turn a simple bulk update into a cursor loop merely because the syntax is available.

DELIMITER $$CREATE OR REPLACE PROCEDURE sp_collect_high_priority(OUT p_seen INT)READS SQL DATABEGIN  DECLARE done INT DEFAULT 0;  DECLARE v_id BIGINT;  DECLARE cur CURSOR FOR    SELECT work_order_id FROM work_orders WHERE priority >= 3 ORDER BY work_order_id;  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;  SET p_seen=0;  OPEN cur;  read_loop: LOOP    FETCH cur INTO v_id;    IF done=1 THEN      LEAVE read_loop;    END IF;    SET p_seen=p_seen+1;  END LOOP;  CLOSE cur;END$$DELIMITER ;SET @seen=0;CALL sp_collect_high_priority(@seen);SELECT @seen AS high_priority_rows;-- Prefer this set-based form when the only goal is counting:SELECT COUNT(*) AS high_priority_rows FROM work_orders WHERE priority >= 3;

The cursor teaches lifecycle and handler semantics, but the final COUNT(*) is the better implementation for this particular goal. A useful routine review asks: “Is the loop expressing business sequencing, or did we accidentally rewrite relational algebra one row at a time?”

6. Production judgment, routine testing, and cleanup

In production, record DEFINER/SQL SECURITY, CREATE ROUTINE/ALTER ROUTINE/EXECUTE grants, binary-log policy, server version and exact failure semantics. Monitor routine latency through statement instrumentation where enabled, lock waits caused by routines, application error rates and replication consequences. Keep routines small enough to test independently; a 500-line routine with nested handlers is not “centralized logic” if nobody can safely reason about it.

1. Call every success path and each expected business error.
2. Confirm rollback leaves no partial history row or base-table change.
3. Capture SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION.
4. Compare cursor and set-based alternatives on representative data before choosing the cursor.
5. Drop the disposable schema when finished.

Lesson 3 adds automatic row-level execution through triggers. The same handler, transaction and definer disciplines now become harder because the application may not even know that extra code ran.