I’m trying to automate database operations across multiple tables that follow a naming pattern. I have around 15 tables with names like ‘dataCOUNTRYinfo’ and need to count specific records in each table, then save those counts to a summary table.
I created this stored procedure but I’m stuck on how to capture the results from the dynamic SQL and store them back into my target table:
CREATE PROCEDURE `DataCounter`()
MODIFIES SQL DATA
BEGIN
DECLARE table_name VARCHAR(1000);
DECLARE country_code VARCHAR(50);
DECLARE counter INT;
DECLARE total_records INT;
DECLARE location_data VARCHAR(1000);
SET table_name = "";
SET country_code = "";
SET counter = 1;
SET total_records = (SELECT COUNT(*) FROM SUMMARY_DATA_TABLE);
WHILE counter <= total_records DO
SET country_code = (SELECT sdt.Country_Code FROM SUMMARY_DATA_TABLE sdt WHERE sdt.Id = counter);
SET @location_data = (SELECT sdt.Location_Info FROM SUMMARY_DATA_TABLE sdt WHERE sdt.Id = 1);
SET @table_name = (SELECT sdt.Table_Name FROM SUMMARY_DATA_TABLE sdt WHERE sdt.Country_Code = country_code);
SET @sql = CONCAT('SELECT SUM(record_count) FROM (SELECT COUNT(dt.Value) AS record_count FROM ', @table_name,' dt WHERE dt.Value != "-99") AS result;');
PREPARE query FROM @sql;
EXECUTE query;
DEALLOCATE PREPARE query;
SET counter = counter + 1;
END WHILE;
END
The procedure runs and shows the counts I want, but I can’t figure out how to capture these results and update my summary table with them. How can I store the dynamic query results into a variable and then use it in an UPDATE statement?
The session variable approach works, but I’ve had better luck with dynamic UNION queries for batch operations like this. Instead of looping through results, build one query that hits all your tables: SELECT 'USA' as country_code, COUNT(*) as record_count FROM dataUSAinfo WHERE Value != '-99' UNION SELECT 'CAN', COUNT(*) FROM dataCANinfo WHERE Value != '-99' etc. Run it once with prepared statements, then INSERT the whole result set into your summary table. This skips the variable capture headaches completely and runs way faster than processing row-by-row. I switched after getting burned by deadlocks from long loops on production DBs. Yeah, you’ll write more complex SQL, but it’s more reliable and faster for bulk stuff.
Stored procedures are a nightmare for repetitive database work like this. I wasted tons of time fighting MySQL’s dynamic SQL limitations when I started out.
Your problem is orchestrating multiple database operations based on patterns and conditions. Skip the prepared statements and session variables - use an automation workflow instead.
I handle this by setting up flows that loop through table metadata, run the counts, and update summary tables automatically. Connect directly to MySQL, build dynamic queries from your table patterns, grab results without prepared statement headaches, and update your summary table in one go.
No more DECLARE statements or session variable hacks. Just database operations that work like they should.
You also get real error handling and can tweak the logic when table patterns change. Much cleaner than maintaining stored procedures.
Prepared statements can’t return values directly to variables in MySQL stored procedures. You’ll need a session variable before running your dynamic SQL. Declare SET @result_count = 0; before your WHILE loop, then change your dynamic query to SET @sql = CONCAT('SELECT COUNT(dt.Value) INTO @result_count FROM ', @table_name,' dt WHERE dt.Value != "-99"'); - I stripped out the subquery since you’re just counting records. After running the prepared statement, grab the result with SET counter_result = @result_count; and use it in your UPDATE. I’ve hit the same wall with dynamic SQL in MySQL and this method works every time for capturing results across multiple loops.
you’re missing the INTO clause in your dynamic SQL to capture the result. change your query to SET @sql = CONCAT('SELECT SUM(record_count) INTO @result_count FROM (SELECT COUNT(dt.Value) AS record_count FROM ', @table_name,' dt WHERE dt.Value != "-99") AS result;'); after executing, you can use the @result_count variable for your update statement.