Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

       IN p_itemname VARCHAR(50),
       IN p_itemvalue smallint(6),      
      INSERT INTO item VALUES('',p_itemname,p_itemvalue);

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?

share|improve this question
2. is done using a proper UNIQUE index on the table. – Jannes Mar 8 '12 at 18:17
  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.

share|improve this answer
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 posting your answer, you agree to the privacy policy and terms of service.

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