http://stackoverflow.com/questions/4862911/how-to-throw-an-error-in-mysql-procedure
9down votefavoriteWhat is the mechanism to force the MySQL to throw an error within the stored procedure?
I have a procedure which call s another function:
PREPARE my_cmd FROM @jobcommand;
EXECUTE my_cmd;
DEALLOCATE PREPARE my_cmd;
the job command is:
jobq.exec("Select 1;wfdlk# to simulatte an error");
then:
CREATE PROCEDURE jobq.`exec`(jobID VARCHAR(128),cmd TEXT)
BEGIN
DECLARE result INT DEFAULT 0;
SELECT sys_exec( CONCAT('echo ',cmd,' | base64 -d > ', '/tmp/jobq.',jobID,'.sh ; bash /tmp/jobq.',jobID,'.sh &> /tmp/jobq.',jobID)) INTO result;
IF result>0 THEN
# call raise_mysql_error(result);
END IF;
END;
My jobq.exec
is always succeeding. Are there way to rise an error? How to implement raise_mysql_error function??
BTW I am using MySQL 5.5.8
thanks Arman.
mysql stored-procedures throw stored-functions shareimprove this question asked Feb 1 '11 at 13:06 Arman 1,89652952 1 related : stackoverflow.com/questions/465727/… – Haim Evgi Feb 1 '11 at 13:09 also read this chapter docstoc.com/docs/687360/Error-Handling-In-Stored-Procedure – Haim Evgi Feb 1 '11 at 13:11 add a commentYes, there is: use the SIGNAL
keyword.
You may use following stored procedure to emulate error-throwing:
CREATE PROCEDURE `raise`(`errno` BIGINT UNSIGNED, `message` VARCHAR(256))
BEGIN
SIGNAL SQLSTATE
'ERR0R'
SET
MESSAGE_TEXT = `message`,
MYSQL_ERRNO = `errno`;
END
Example:
CALL `raise`(1356, 'My Error Message');