Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question
    
2. is done using a proper UNIQUE index on the table. –  Jannes Mar 8 '12 at 18:17
add comment

1 Answer 1

  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
add comment

Your Answer

 
discard

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.