I recently spent 3 hours pulling my hair out trying to fix a MySQL stored procedure that wasn’t working as expected. I’ve boiled the ultimate problem down to a simple example below. Can you spot the problem?
# First, let's create some sample data
CREATE TABLE TrendicsTest (
DateTime DATETIME NOT NULL,
Value INT NOT NULL,
UNIQUE KEY DateTime (DateTime)
);
INSERT INTO TrendicsTest VALUES ('2008-01-01 01:00', 1);
INSERT INTO TrendicsTest VALUES ('2008-01-01 02:00', 2);
# Next, lets' define a stored procedure to query the sample
data
DELIMITER |
DROP PROCEDURE IF EXISTS summarizeTrendicsTest|
CREATE PROCEDURE summarizeTrendicsTest(dateTime DATETIME)
BEGIN
SELECT @endDateTime := DATE_ADD(dateTime, INTERVAL 1 HOUR);
SELECT * FROM TrendicsTest WHERE DateTime=@endDateTime;
END;
|
DELIMITER ;
…
[Read more]