Menu
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?
733
We can display error message in case of an error generated by MySQL query. This meaning full error message gives idea one the problem or bugs in the script. We can print the error message by using mysql function mysql_error(). This function returns the error message associated with most recently executed query.
To modify an existing stored routine (procedure or function), double-click the node of the routine to modify, or right-click this node and choose the Alter Routine command from the context menu. Either of the commands opens the SQL Editor.
Instead, use mysql_error() to retrieve the error text. Note that this function only returns the error text from the most recently executed MySQL function (not including mysql_error() and mysql_errno()), so if you want to use it, make sure you check the value before calling another MySQL function.
For SQLEXCEPTION conditions, the stored program terminates at the statement that raised the condition, as if there were an EXIT handler. If the program was called by another stored program, the calling program handles the condition using the handler selection rules applied to its own handlers.
MySQL 5.5 introduces signals, which are similar to exceptions in other languages:
http://dev.mysql.com/doc/refman/5.5/en/signal.html
For example, in the mysql command line client:
mysql> SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Custom error'; ERROR 1644 (45000): Custom error 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:
DROP PROCEDURE IF EXISTS decount_test; DELIMITER // 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. | +----------------------------------------------------+ If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
