It is not often that i use MySQL Stored procedures, but it seems for my latest database task it seems there was no getting around it. There are several concepts covered in the sample code below, including stored procedures, handling strings in mysql and the quote() function.
What it Does
The rest is just one big query that’s converted to a string and concatenated. It takes data from customer tables and creates a temp table to allow me to do some fix-up later. I had multiple customer tables and did not want to have to copy manually change table names and dates for each call. Also each call takes about an hour to complete so i wanted them all queued so i could let it run overnight.
Here is the code.
DROP PROCEDURE IF EXISTS Polling_CreateTempFixTable;
CREATE PROCEDURE Polling_CreateTempFixTable(
vCustomerID int unsigned,
set @sql =
'CREATE table PollingFix', vCustomerID,
store_id as `StoreID`
,date_format(date_charged, \'%Y-%m-%d %H\') as `DateHour`
,count(store_id) as `PostingsCount`
,polling as PollingInterval',
' FROM store_data_', vCustomerID,
' WHERE date_charged BETWEEN ', quote(vDateStart), ' and ', quote(vDateEnd),
' GROUP BY
,date_format(date_charged, \'%Y-%m-%d %H\')',
' ORDER BY
date_format(date_charged, \'%Y-%m-%d %H\')'
prepare query FROM @sql;
deallocate prepare query;
The main point here is that i can create tables dynamically by simply passing in vCustomerID. (
CREATE table PollingFix', vCustomerID,). I was able to create tables for multiple customers called PollingFix1, PollingFix2 etc. Similarly i was able to call from matching source tables (
FROM store_data_', vCustomerID,).