CODEWITHSUNDEEP
mysqlstored-procedureserror-handling
borjab
borjab

Reputation: 11665

How to parametrize an error in Mysql stored procedure

I am programming the error handling in a MySQL stored procedure using SIGNAL. I want to parametrize the error message to make it more helpful. For example

 IF vMyVariable IS NULL THEN
            SIGNAL SQLSTATE VALUE '45006'
                SET MESSAGE_TEXT = ('Found null variable in iteration' || vId); 
 END IF;

I tried to use concat() and didn't work.

Upvotes: 2

Views: 42

Answers (1)

Marc Alff
Marc Alff

Reputation: 8395

See https://dev.mysql.com/doc/refman/5.5/en/signal.html and in particular the section about simple_value_specification

Arbitrary expression can not be used when assigning attributes with SET, as in:

SIGNAL SQLSTATE VALUE '45006' SET MESSAGE_TEXT = <sorry, no arbitrary expressions here>;

However, a local variable is a simple_value_specification, so the following does work:

DECLARE my_message_text VARCHAR(64);
my_message_text := <anything you want>;
SIGNAL SQLSTATE VALUE '45006' SET MESSAGE_TEXT = my_message_text;

Upvotes: 2

Related Questions