CODEWITHSUNDEEP
mysqlsqlmysql-workbench
Arun nagar
Arun nagar

Reputation: 176

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

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: 0

Views: 7577

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176084

Use SIGNAL SQLSTATE:

CREATE PROCEDURE `p`(divisor INT)
 BEGIN
 Declare MESSAGE_TEXT varchar(200);
 IF divisor = 0 THEN
   BEGIN
     SIGNAL SQLSTATE '45000'
     set MESSAGE_TEXT='error occured in if block';
   END;
 END IF;
 END

CALL `p`(0)
-- error occured in if block

SqlFiddleDemo

Upvotes: 3

Related Questions