Exception handling in MySQL Stored Procedures

 Exception handling in MySQL Stored Procedures

--  Replace the table name,columns names according to your requirement

-- drop procedure sp_med_master_del

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_med_master_del`(
IN  P_ID INT
)
BEGIN
       
DECLARE exit handler for sqlexception
BEGIN
ROLLBACK;
SELECT 'Delete Medicine  failed ...';
                        RESIGNAL;   -- throws the actual sql error
END;

DECLARE exit handler for sqlwarning
BEGIN
ROLLBACK;
                        SELECT 'Delete Medicine  failed ...';
                        RESIGNAL;            
END;
            
START TRANSACTION;
    

    delete from  `cli.medicine_mstr` 
    WHERE ID=P_ID;
            
COMMIT;
END$$
DELIMITER ;