CODEWITHSUNDEEP
mysqlstored-proceduresexception
Cheikh Ahmadou
Cheikh Ahmadou

Reputation: 59

Mysql : Throw an exception inside procedure

I have this code below that should throw an exception if amount is negative : 

create procedure depositMoney(
        IN acc integer,
        IN amoun integer
    )
    BEGIN
        DECLARE negative CONDITION FOR SQLSTATE '45000';
        IF amoun < 0 THEN SIGNAL negative;
        END IF;
        INSERT INTO account(balance) VALUES amoun where account.number = acc;
    END;

but when i execute i have an #1064 error: 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6

Could you help me ?

Upvotes: 0

Views: 2687

Answers (1)

wchiquito
wchiquito

Reputation: 16551

Check the syntax of the INSERT.

Try:

DELIMITER //

DROP PROCEDURE IF EXISTS `depositMoney`//

CREATE PROCEDURE `depositMoney` (
  IN `acc` INTEGER,
  IN `amoun` INTEGER
)
BEGIN
  DECLARE `negative` CONDITION FOR SQLSTATE '45000';
  IF `amoun` < 0 THEN
    SIGNAL `negative`
    SET MESSAGE_TEXT = 'An error occurred';
  END IF;
  /*
  Check the syntax of the INSERT.
  INSERT INTO account(balance) VALUES amoun where account.number = acc;
  */
END//

DELIMITER ;

MySQL 5.5 or higher.

Test:

mysql> CALL `depositMoney`(NULL, -1);
ERROR 1644 (45000): An error occurred

Upvotes: 2

Related Questions