MySQL transaction within stored procedure example

sql.md

Create stored procedure :

DROP PROCEDURE IF EXISTS sp_delete_users_till_date;

DELIMITER //

CREATE PROCEDURE sp_delete_users_till_date(location_id INT, till_date DATE)

BEGIN
    
    DECLARE track_no INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, NOT FOUND, SQLWARNING
    
    BEGIN
    
        ROLLBACK;
        GET DIAGNOSTICS CONDITION 1 @`errno` = MYSQL_ERRNO, @`sqlstate` = RETURNED_SQLSTATE, @`text` = MESSAGE_TEXT;
        SET @full_error = CONCAT('ERROR ', @`errno`, ' (', @`sqlstate`, '): ', @`text`);
        SELECT track_no, @full_error;
    
    END;

    START TRANSACTION;

        SET FOREIGN_KEY_CHECKS = 0;
        SET track_no = 1;

        DELETE FROM users WHERE users.location_id = location_id AND DATE(users.created_at) <= till_date;
        SET track_no = 2;

        SET FOREIGN_KEY_CHECKS = 1;
        SET track_no = 3;

        SELECT track_no, 'Congrates!, successfully executed.';

    COMMIT;

END; //

DELIMITER ;

Call stored procedure :

call sp_delete_users_till_date(2, '2019-12-17');