Print affected rows in mySQL Stored Procedure

Admin 1
Viewed 90

we have this Stored Procedure to delete related tables. How can I print/display affected rows on each table?

mysql sql
1 Answers

You can use ROW_COUNT() and delete statements like this

CREATE DEFINER=`root`@`localhost` PROCEDURE `DELETE_TABLES`(IN _id INT)
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    SHOW ERRORS;
      ROLLBACK;
END;

START TRANSACTION;
DELETE   tableA WHERE tableA.id = _id;
SELECT ROW_COUNT() AS 'Affected rows on A';

DELETE   tableB WHERE tableB.pan = _id;
SELECT ROW_COUNT() AS 'Affected rows on B';

DELETE   tableC WHERE tableC.pan = _id;
SELECT ROW_COUNT() AS 'Affected rows on C';

DELETE   tableD WHERE tableD.pan = _id;
SELECT ROW_COUNT() AS 'Affected rows on D';

DELETE   tableE WHERE tableE.id = _id;
SELECT ROW_COUNT() AS 'Affected rows on E';

COMMIT;

END
user_57028110
Related