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

Learning outcomes

A stored procedure is not “SQL in a magic box,” and DELIMITER is not part of the stored procedure language. This lesson separates client parsing from server-side routine syntax, then builds a procedure whose success and failure paths are observable and testable.

A realistic problem: one business operation, several client steps

Closing a work order should validate its current state, write an audit record, set closed_at, and either commit all of that or commit none of it. If every application implements those steps differently, correctness drifts. A stored procedure can provide one server-side operation—but it must make failure semantics and execution context explicit.

Client delimiter versus server syntax

The mysql command-line client normally sends a statement when it sees ;. A routine body contains many semicolons, so the client needs a temporary terminator while you type the whole CREATE PROCEDURE. DELIMITER is therefore a mysql-client command. It is not stored in the routine and should not be sent through application drivers that already send complete statement strings.

USE servicehub_logic_lab;DELIMITER $$CREATE PROCEDURE p_demo(IN p_value INT, OUT p_double INT)BEGIN  SET p_double = p_value * 2;END$$DELIMITER ;CALL p_demo(7,@answer);SELECT @answer AS doubled;SHOW CREATE PROCEDURE p_demo\GDROP PROCEDURE p_demo;

Expected result: @answer is 14. The server never needed to understand the word DELIMITER; the client used it to decide when the CREATE PROCEDURE text was complete.

Build a transactional close-work-order procedure

Create this routine while connected as logic_owner. The default routine security mode is DEFINER, so the routine executes underlying statements using its definer privileges unless you explicitly choose SQL SECURITY INVOKER.

USE servicehub_logic_lab;DELIMITER $$CREATE PROCEDURE close_work_order(  IN p_work_order_id BIGINT UNSIGNED,  IN p_note VARCHAR(180),  OUT p_result VARCHAR(255))SQL SECURITY DEFINERMODIFIES SQL DATABEGIN  DECLARE v_status VARCHAR(20);  DECLARE v_errno INT DEFAULT 0;  DECLARE v_sqlstate CHAR(5) DEFAULT '00000';  DECLARE v_message TEXT DEFAULT '';  DECLARE EXIT HANDLER FOR SQLEXCEPTION  BEGIN    GET STACKED DIAGNOSTICS CONDITION 1      v_sqlstate = RETURNED_SQLSTATE,      v_errno = MYSQL_ERRNO,      v_message = MESSAGE_TEXT;    ROLLBACK;    SET p_result = CONCAT('ERROR ',v_errno,' [',v_sqlstate,'] ',v_message);  END;  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 MYSQL_ERRNO = 31001,          MESSAGE_TEXT = 'Work order does not exist';  END IF;  IF v_status = 'closed' THEN    SIGNAL SQLSTATE '45000'      SET MYSQL_ERRNO = 31002,          MESSAGE_TEXT = 'Work order is already closed';  END IF;  UPDATE work_orders  SET status='closed', closed_at=CURRENT_TIMESTAMP(6)  WHERE work_order_id=p_work_order_id;  INSERT INTO work_order_audit    (work_order_id,old_status,new_status,change_source,changed_by)  VALUES    (p_work_order_id,v_status,'closed',     CONCAT('procedure:',COALESCE(p_note,'')),CURRENT_USER());  COMMIT;  SET p_result = 'OK';END$$DELIMITER ;

FOR UPDATE locks the selected work-order row within the transaction so a concurrent close operation cannot casually race past the state check. The exit handler captures the condition from the handler stack, rolls the transaction back, and returns a deterministic diagnostic string for this teaching lab.

Invoke, observe, and verify

SET @result=NULL;CALL close_work_order(1,'dispatcher close',@result);SELECT @result AS procedure_result;SELECT work_order_id,status,closed_atFROM work_orders WHERE work_order_id=1;SELECT audit_id,work_order_id,old_status,new_status,change_source,changed_byFROM work_order_audit WHERE work_order_id=1 ORDER BY audit_id;

The work order and audit row should change together. Now call the routine again for the already-closed order:

SET @result=NULL;CALL close_work_order(1,'duplicate close attempt',@result);SELECT @result AS procedure_result;SELECT COUNT(*) AS audit_rowsFROM work_order_audit WHERE work_order_id=1;

The second call should report the custom 31002 condition and should not add another audit row. This proves more than “the procedure printed an error”: the rollback preserved the expected database state.

SIGNAL, RESIGNAL, and GET DIAGNOSTICS

SIGNAL deliberately raises a condition. GET DIAGNOSTICS reads the diagnostics area; GET STACKED DIAGNOSTICS is available inside a handler to inspect the condition that activated it. RESIGNAL rethrows the current condition, optionally changing selected condition information.

DROP PROCEDURE IF EXISTS demonstrate_resignal;DELIMITER $$CREATE PROCEDURE demonstrate_resignal()BEGIN  DECLARE v_message TEXT;  DECLARE EXIT HANDLER FOR SQLEXCEPTION  BEGIN    GET STACKED DIAGNOSTICS CONDITION 1 v_message = MESSAGE_TEXT;    SET v_message = CONCAT('demonstrate_resignal: ',v_message);    RESIGNAL SET MESSAGE_TEXT = v_message;  END;  SIGNAL SQLSTATE '45000'    SET MESSAGE_TEXT='Intentional Chapter 10 diagnostic failure';END$$DELIMITER ;CALL demonstrate_resignal();-- The CALL returns the same SQLSTATE with the handler-enriched message.

This example deliberately avoids a logging side effect: it demonstrates stacked diagnostics and rethrow semantics without creating a second transaction question. If you need durable error telemetry, design that channel explicitly—application logs, server logs, or a separately owned persistence path—rather than assuming a handler insert will survive every rollback boundary.

IN, OUT, and INOUT parameters

DELIMITER $$CREATE PROCEDURE add_retry(INOUT p_attempts INT)BEGIN  SET p_attempts = COALESCE(p_attempts,0) + 1;END$$DELIMITER ;SET @attempts=2;CALL add_retry(@attempts);SELECT @attempts AS attempts_after_call;DROP PROCEDURE add_retry;

Stored functions: useful, but a narrower contract

A stored function can be useful for deterministic scalar logic that is genuinely better located with the data. It can also become dangerous when it hides expensive queries inside row-by-row expressions. Keep the example side-effect-free. Before creating it, inspect binary-log policy:

SHOW VARIABLES LIKE 'log_bin';SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

If binary logging is enabled and log_bin_trust_function_creators=OFF, current MySQL requires the creator to have SUPER in addition to the normal routine privilege. That setting is a replication/recovery safety policy. Do not switch it globally to ON just to complete a tutorial. Run the following function creation as your disposable local administrator if the restricted logic_owner account receives error 1419. The function is explicitly DETERMINISTIC and NO SQL, which documents its behavior but does not bypass the creator-privilege rule.

DELIMITER $$CREATE FUNCTION priority_label(p_priority TINYINT UNSIGNED)RETURNS VARCHAR(12)DETERMINISTICNO SQLRETURN CASE p_priority  WHEN 1 THEN 'critical'  WHEN 2 THEN 'high'  WHEN 3 THEN 'normal'  ELSE 'low'END$$DELIMITER ;SELECT work_order_id,priority,priority_label(priority) AS priority_labelFROM work_orders ORDER BY work_order_id;SHOW CREATE FUNCTION priority_label\G

The DETERMINISTIC and NO SQL characteristics document properties of this function; they are not a free performance guarantee. Stored functions also have documented restrictions, including no recursion and constraints on modifying tables involved in the invoking statement.

Routine metadata and least privilege

SHOW PROCEDURE STATUS WHERE Db='servicehub_logic_lab';SHOW FUNCTION STATUS WHERE Db='servicehub_logic_lab';SHOW CREATE PROCEDURE close_work_order\GSHOW CREATE FUNCTION priority_label\GSELECT ROUTINE_NAME,ROUTINE_TYPE,DATA_ACCESS,SECURITY_TYPE,DEFINERFROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_SCHEMA='servicehub_logic_lab'ORDER BY ROUTINE_TYPE,ROUTINE_NAME;

To use a routine, a caller needs the appropriate EXECUTE privilege. A definer-security routine can intentionally expose a narrow operation without granting broad table privileges, but that makes routine review and definer lifecycle security-critical. Do not casually grant CREATE ROUTINE or broad schema DML to application accounts.

The wrong assumption: “an SQL error rolls back everything”

A stored routine does not suspend MySQL transaction rules. Some errors roll back only the failing statement, and many DDL statements cause implicit commits or cannot be rolled back. Mixing routine-definition DDL with business DML and expecting one outer ROLLBACK to erase all effects is unsafe.

Hands-on lab acceptance checklist

• You can explain why DELIMITER belongs to the mysql client.
• close_work_order updates one order and writes one audit row in one successful transaction.
• A second close attempt activates the handler and does not add an extra audit row.
• SHOW CREATE and INFORMATION_SCHEMA.ROUTINES expose the recorded routine security/definer metadata.
• The scalar function remains read-only and its data-access characteristic is documented.

Production judgment and next step

Stored procedures are most defensible when they encapsulate a stable transactional operation shared by multiple clients, enforce a narrow privilege boundary, or reduce round trips without creating an opaque monolith. Stored functions are best kept small, predictable, and inexpensive when called from set-oriented queries. Version routine definitions, test error paths, expose SQLSTATE/error contracts, and monitor expensive statements executed inside routines.

Lesson 3 examines an even less visible form of server-side behavior: triggers that fire automatically when rows change.

Authoritative references

• MySQL 8.4 — CREATE PROCEDURE and CREATE FUNCTION
• MySQL 8.4 — Stored Routine Syntax
• MySQL 8.4 — Condition Handling
• MySQL 8.4 — GET DIAGNOSTICS
• MySQL 8.4 — Restrictions on Stored Programs