Before committing changes with a mysql script to a database, I want to perform additional checks.
I want to rollback a transaction, when the count of records returned by a sql statement is not between the limits given in the call.
The procedure should be called like

CALL my_database.checkSQLCount(
“SELECT count(*) FROM my_table WHERE field1=’xxx'”,
30,
35);

I made a similar procedure for a check of the number of deletes/updates/inserts:

DELIMITER //

DROP PROCEDURE IF EXISTS my_database.checkAffectedRows;
CREATE PROCEDURE my_database.checkAffectedRows(IN sqlStatement VARCHAR(1000), IN minAffectedRows INT, IN maxAffectedRows INT)
BEGIN
DECLARE success INT;
DECLARE affectedRows INT;

SET @sql := sqlStatement;
— Print current SQL statement
SELECT @sql AS ‘SQL statement:’;

— Execute the provided SQL statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
SELECT ROW_COUNT() INTO affectedRows;

DEALLOCATE PREPARE stmt;

— If the affected rows are outside the specified range, set the success flag to 0
IF affectedRows < minAffectedRows OR affectedRows > maxAffectedRows THEN
SELECT CONCAT(‘ERROR: ‘, affectedRows, ‘ not between ‘, minAffectedRows, ‘ and ‘, maxAffectedRows)
AS ‘Check expected nr updates’;
SET success := 0;
ELSE
SELECT CONCAT(‘OK: ‘, affectedRows, ‘ is between ‘, minAffectedRows, ‘ and ‘, maxAffectedRows)
AS ‘Check expected nr updates’;
SET success := 1;
END IF;

— Insert the result into the CallResults table
INSERT INTO CallResults (success) VALUES (success);
END //

DROP PROCEDURE IF EXISTS my_database.handleCallResults;
CREATE PROCEDURE my_database.handleCallResults()
BEGIN
— Check if any of the calls failed, if so, rollback the transaction
IF (SELECT COUNT(*) FROM CallResults WHERE success = 0) > 0 THEN
ROLLBACK;
SELECT ‘Transaction rolled back’ AS ‘=== Data patch end result ===’;
ELSE
— No Rollback for above calls? Commit!
COMMIT;
SELECT ‘Transaction committed’ AS ‘=== Data patch end result ===’;
END IF;
END //

DELIMITER ;

— Start transaction
START TRANSACTION;

— Update table but rollback when amount of updates is not between 1 and 100
CALL my_database.checkAffectedRows(
“UPDATE myTable SET value=’xxx’ WHERE idMyTable < 100”,
1,100);

I tried a procedure like this:

DROP PROCEDURE IF EXISTS bahman.checkSQLCount;
CREATE PROCEDURE myDatabase.checkSQLCount(IN countQuery VARCHAR(1000), IN minCount INT, IN maxCount INT)
BEGIN
DECLARE success INT;
DECLARE rowCount INT;

SELECT countQuery AS ‘SQL statement:’;

— Execute the provided SQL statement without fetching results
SET @stmt = countQuery;
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

— Retrieve the number of affected rows using FOUND_ROWS() or with ROW_COUNT()
SELECT FOUND_ROWS() INTO rowCount;

— If the matched rows are outside the specified range, set the success flag to 0
IF rowCount < minCount OR rowCount > maxCount THEN
SELECT CONCAT(‘ERROR: ‘, rowCount, ‘ not between ‘, minCount, ‘ and ‘, maxCount)
zzAS ‘Check expected nr matched rows’;
SET success := 0;
ELSE
SELECT CONCAT(‘OK: ‘, rowCount, ‘ is between ‘, minCount, ‘ and ‘, maxCount)
AS ‘Check expected nr matched rows’;
SET success := 1;
END IF;

Insert the result into the CallResults table
INSERT INTO CallResults (success) VALUES (success);
END //

When I call this with

CALL my_database.checkSQLCount(
“SELECT * FROM my_table WHERE field1=’xxx'”,
30,
35);
the output will show all selected rows, which I don’t want to see.
And when I change it into
CALL my_database.checkSQLCount(
“SELECT count(*) FROM my_table WHERE field1=’xxx'”,
30,
35);
the result is that I have found one record (containing the count)