Raising / throwing custom exceptions in MySQL

I failed to find an official way to explicitly raise or throw exceptions in stored procedures in MySQL.

My workaround works by calling an undefined function with a meaningful name for an error.

For example:

view plainprint?

1. DROP TRIGGER IF EXISTS users_before_insert;  
2. DROP TRIGGER IF EXISTS users_before_update;  
3. DROP PROCEDURE IF EXISTS validate_password;  
4.   
5. delimiter $$  
6. CREATE PROCEDURE validate_password (IN passwd VARCHAR(64))  
7. BEGIN  
8.  IF LENGTH(passwd) < 5 THEN  
9.   CALL TRIGGER_DUMMY_INVALID_PASSWORD;  
10.  END IF;  
11. END$$  
12.   
13. CREATE TRIGGER users_before_insert BEFORE INSERT ON users  
14.  FOR EACH ROW  
15.  BEGIN  
16.   CALL validate_password(NEW.password_cleartext);  
17.  END$$  
18.   
19. CREATE TRIGGER users_before_update BEFORE UPDATE ON users  
20.  FOR EACH ROW  
21.  BEGIN  
22.   CALL validate_password(NEW.password_cleartext);  
23.  END$$  
24. delimiter ;  

DROP TRIGGER IF EXISTS users_before_insert;
DROP TRIGGER IF EXISTS users_before_update;
DROP PROCEDURE IF EXISTS validate_password;

delimiter $$
CREATE PROCEDURE validate_password (IN passwd VARCHAR(64))
BEGIN
 IF LENGTH(passwd) < 5 THEN
  CALL TRIGGER_DUMMY_INVALID_PASSWORD;
 END IF;
END$$

CREATE TRIGGER users_before_insert BEFORE INSERT ON users
 FOR EACH ROW
 BEGIN
  CALL validate_password(NEW.password_cleartext);
 END$$

CREATE TRIGGER users_before_update BEFORE UPDATE ON users
 FOR EACH ROW
 BEGIN
  CALL validate_password(NEW.password_cleartext);
 END$$
delimiter ;

Most of the code in the specific example above was actually written by my colleague Per Fuglsang Møller.