1

I want to create a procedure in which all types of possible errors can be handled using SIGNAL. Below is my stored procedure:

DROP PROCEDURE IF EXISTS `spItem`$$
CREATE PROCEDURE `spItem`(
       IN p_itemname VARCHAR(50),
       IN p_itemvalue smallint(6),      
       )
BEGIN
      INSERT INTO item VALUES('',p_itemname,p_itemvalue);
END$$

How can we use the SIGNAL statement for each kind of error in the above stored procedure that checks for the following:

1. check for any error in the insert statement
2. check for duplicate item names
3. check for valid item values (as it should just accept numeric values).

What changes will be required in the above stored procedure to handle the above errors?

  • 2. is done using a proper UNIQUE index on the table. – Jannes Mar 8 '12 at 18:17
2
  1. This is done using a proper UNIQUE index on the table itself. MySQL will automatically take care of it.
  2. This is done using proper data types for the fields in the table itself. MySQL will automatically take care of it. (BTW: your item_value is already smallint(6) so it can only be numeric anyway.)

  3. Seeing the obove two points, you probably don't need to check this anymore.

Meaning you probably not need your stored procedure anymore. At least not for this reason.

  • Actually i wanted track each exception using SIGNAL and wanted to return related error message. – neeraj Mar 12 '12 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.