Basecode.net

My first stored procedure in MySQL. I hope it doesnt open a can of worms for me. I’ve already experienced the pitfalls of putting too much logic in the database (SP’s) and don’t intend on allowing history to repeat itself.

CREATE PROCEDURE CreateReportUnits(IN num INT, unit VARCHAR(20))
SQL SECURITY INVOKER
BEGIN
 TRUNCATE test.report_days;
 SET @i = 0;
 WHILE @i < num DO
 SET @i = @i + 1;
 SET @query = CONCAT("INSERT INTO test.report_days ( report_day_timestamp ) VALUES ( DATE_SUB(CURDATE(),INTERVAL ",@i," ",unit,") );");
 PREPARE statement FROM @query;
 EXECUTE statement;
 END WHILE;

END

# To invoke use:
CALL CreateReportUnits(6 'MONTH');

The invocation will create a record for the last 6 months based on today’s date to use in reports.

Here are a few MySQL commands I used recently which were quite handy.

FLUSH TABLES {dbName}.{tableName};

This one clears the query cache for the specified table.

SHOW TABLE STATUS FROM {dbName} LIKE '{tableName}';

This one shows useful data such as Auto increment, length and modified time of a table.

SHOW TABLES FROM {dbName} LIKE '%{tableName}%';

This lists the tables matching the criteria in the LIKE clause.