Mysql – use of signal for insert statement in thesql stored procedure

MySQLstored-procedures

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?

Best Answer

  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.

Related Question