Stored Procedures & Functions

Stored Procedures and Functions

Stored procedures are precompiled SQL programs stored server-side, reducing network round-trips and enabling encapsulation of complex multi-step operations.

Procedure vs Function

• Procedure: called with CALL, can have IN/OUT/INOUT params, returns result sets
• Function: called in SQL expressions, returns a single value, must be DETERMINISTIC or NON-DETERMINISTIC

Control Flow

MySQL supports IF/ELSEIF/ELSE, CASE, WHILE, LOOP/LEAVE/ITERATE, and REPEAT/UNTIL. Cursors enable row-by-row processing but are slow — prefer set-based SQL when possible.

Error Handling

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Operation failed';
END;

Triggers

Use BEFORE triggers for validation and auto-populating fields. Use AFTER triggers for audit logging. Avoid calling stored procedures from triggers — it makes debugging very difficult.

Event Scheduler

SET GLOBAL event_scheduler = ON;
CREATE EVENT cleanup ON SCHEDULE EVERY 1 DAY STARTS '2024-01-01 02:00:00'
DO DELETE FROM expired_sessions WHERE expires_at < NOW() LIMIT 5000;

When to Use

Good for: multi-step atomic operations (transfers), shared business rules across apps, reducing round-trips. Bad for: frequently-changing logic (hard to deploy), complex algorithms (hard to debug).