CODEWITHSUNDEEP
mysqlsqlstored-procedures
Mitchell
Mitchell

Reputation: 343

How to tell if mysql stored procedure executed successfully

I have a stored procedure that updates two tables and then inserts a row in to a third table. (And to my understanding, rolls back the updates or insert if all three cannot be done) Anyway, whenever I run this procedure, regardless if it ends up successfully running the two updates and insert or not, it will always be "successful" and return false. I need it to return a value (probably true) if it successfully does all two updates and one insert and false if it does not so I can do the correct error checking on the server side.

Here is the procedure: 

CREATE DEFINER=`db1`@`%` PROCEDURE `completed_procedure`(IN insubmissionid INT)
BEGIN

DECLARE ingregid INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;

START TRANSACTION;

SET @ingregid = (SELECT idgreg FROM submission WHERE idsubmission = insubmissionid);

UPDATE submission SET status='a' WHERE idsubmission = insubmissionid;

UPDATE greg SET iscomplete=1 WHERE idgreg = @ingregid;

INSERT INTO completion (idperson, idgreg, datecompleted, idsubmission, mediaid, description,
    privatestatus, contenttype, totalamount)  
SELECT s.idperson, s.idgreg, s.datesubmitted, s.idsubmission,
    COALESCE(s.youtubeid, s.contentid) AS mediaidid, g.description, g.privatestatus,
    g.contenttype, COALESCE(SUM(amount),0) AS totalamount 
FROM submission s 
INNER JOIN greg g on s.idgreg = g.idgreg
INNER JOIN contribution c on s.idgreg = c.idgreg 
WHERE s.idsubmission = insubmissionid AND c.ispaid = 1;

COMMIT;
END

What is the best way to go about this?

Upvotes: 1

Views: 5352

Answers (1)

Agu V
Agu V

Reputation: 2281

You could return a value, heres an example.

 CREATE DEFINER=`db1`@`%` PROCEDURE `completed_procedure`(IN insubmissionid INT, OUT success INT
BEGIN

IF (   CONDITION  ) THEN
    SET success = 1;
ELSE
    SET success = -1;
END IF;
SELECT success ;

COMMIT;
END

Upvotes: 0

Related Questions