CODEWITHSUNDEEP
mysqlsql
Arun nagar
Arun nagar

Reputation: 176

The System Variable "MESSAGE_TEXT" is not working with SIGNAL statement in Mysql?

My code is:i am using mysql 5.6 and workbench 6.3CE

 CREATE DEFINER=`root`@`windows7test-pc` PROCEDURE `p`(divisor INT)
 BEGIN
 Declare MESSAGE_TEXT varchar(200);
 IF divisor = 0 THEN
 BEGIN
  DECLARE my_error CONDITION FOR SQLSTATE '45000';
  SIGNAL my_error;
  set MESSAGE_TEXT='error occured in if block';
 END;
 END IF;

 END

when i apply this stored procedure then i get no error but when i run this by calling call p(0) statement then i get only system error message which is Error Code: 1644 Unhandled user-defined exception condition.but my question is that why the mysql server not generate my error message whic is MESSAGE_TEXT='error occured in if block'; ?

Upvotes: 1

Views: 3999

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179364

You do not declare message_text as a variable.

You do not DECLARE ... CONDITION.

You do have to include the values to SET in the same statement as SIGNAL.

The syntax is simply this:

IF divisor = 0 THEN
  SIGNAL SQLSTATE '45000' SET message_text = 'division: ur doing it wrong';
END IF;

Upvotes: 3

Related Questions