raise error within MySql function

Question

Hi,

I've created a MySql function and would like to raise an error if the values passed for the parameters are invalid. What are my options for raising an error within a MySql function?

Thanks, Don

Answer 1

You have to define exception handlers . Take a look at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

Answer 2

In MySQL 5 you may raise an error by calling a stored procedure that does not exist (CALL raise_error) or passing an invalid value to a query (like null to a NOT NULL contrained field). Here is an interesting post by Roland Bouman on raising errors from within a MySQL function:

http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html

Answer 3

You can also call an existing function with an invalid number of arguments.

Answer 4

It's actually a combination of all three answers. You call a non-existent procedure to raise the error, and then declare an exit handler that catches the error you generated. Here's an example, using SQLSTATE 42000 (procedure does not exist) to throw an error before deletion if the row to be deleted has a foreign key id set:

DELIMITER //

DROP PROCEDURE IF EXISTS decount_test;
CREATE DEFINER = 'root'@'localhost' PROCEDURE decount_test ( p_id bigint )
DETERMINISTIC MODIFIES SQL DATA
BEGIN
  DECLARE EXIT HANDLER FOR SQLSTATE '42000'
    SELECT 'Invoiced barcodes may not have accounting removed.';
  IF (SELECT invoice_id 
       FROM accounted_barcodes
       WHERE id = p_id
    ) THEN
    CALL raise_error;
 END IF;
 DELETE FROM accounted_barcodes WHERE id = p_id;
END //

DELIMITER ;

Output:

call decount_test(123456);
+----------------------------------------------------+
| Invoiced barcodes may not have accounting removed. |
+----------------------------------------------------+
| Invoiced barcodes may not have accounting removed. | 
+----------------------------------------------------+