MySQL - How to throw exception in stored procedure?

Question

How to generate an exception in the stored procedure in MySQL? For example:

CREATE PROCEDURE SALES() BEGIN  STATEMENT... STATEMENT... STATEMENT...  IF (PRICE >= 500) THEN /** THROWS AN EXCEPTION....       WHAT DO TO STOP THE PROCEDURE. **/ END IF;  STATEMENT... STATEMENT... STATEMENT...  END; 

In MySQL I think there is no way to throw an exception in a stored procedure, but I can force an error by selecting from a non-existing table. For example:

IF (PRICE > 500) THEN     /*throw the error here*/     SELECT * FROM price_greater_than_500_in_throw_exception; END IF; 

Is there a more elegant way?

Thanks.

Answer 1

Since MySQL 5.5 you can use SIGNAL and RESIGNAL for error handling. Prior to that there was no way to handle errors in MySQL. Only way is to run an erroneous query (for example inserting into non existing table).

Answer 2

Here is an example of how to throw an exception in mysql, which works on versions 5.5 and above:

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Custom error'; 

Details can be found here: https://dev.mysql.com/doc/refman/5.5/en/signal.html