I have a complex stored procedure (inserts, updates, etc...). The logic wrapped in transaction and if there is any issue I roll back.
Here is a simplified example:
CREATE PROCEDURE `testing`(IN _recordID INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
@p2 = MESSAGE_TEXT;
SELECT @p2;
ROLLBACK;
END;
START TRANSACTION;
IF (EXISTS(SELECT user FROM tblData WHERE recordID = _recordID)) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'There is protected data';
END IF
DELETE FROM tblData WHERE recordID = _recordID
COMMIT;
END
Technically the stored procedure will run successfully in any case (until server is available and table exists). I want to throw error message from MySQL to Node.js server announcing that there was an exception. I use mysql2-promise package on the application server.
Taylor made solution such as parsing returned rows is not an option since I'm looking for a robust solution for production system.