Creating temporary table inside stored procedure

Question

I am working on writing my first stored procedure for MySQL.

The stored procedure needs to create a temporary table and then insert some data into it.

If I manually create a normal table, outside of the stored proc, so the stored proc isn't creating the temporary table the stored proc is created and does exactly what I'm after.

If I then add the create temporary table to the stored proc instead, MySQL workbench then shows unexpected end and the en of the create statement.

Below is my stored proc

    DELIMITER //
    CREATE PROCEDURE getLast24Hours()
    BEGIN
#End of the below line I get unexpected end without this it works fine
        CREATE TABLE hours (`Hour` VARCHAR(8) NOT NULL, `Value` INT NOT NULL DEFAULT 0, PRIMARY KEY(`Hour`)); 

        DECLARE crt_time VARCHAR(8);
        DECLARE counter INT;
        DECLARE currentTime DATETIME;

        SET currentTime = NOW();

        SET crt_time = DATE_FORMAT(currentTime, '%d-%m %H');
        SET counter = 0;
        WHILE counter < 23 DO
            INSERT INTO hours (`Hour`) VALUES (crt_time);
            SET currentTime = currentTime - INTERVAL 1 HOUR;
            SET crt_time = DATE_FORMAT(currenttime, '%d-%m %H');
            SET counter = counter + 1;
        END WHILE;
    END //
    DELIMITER ;

I can't see anything to say that creating a temporary table isn't possible so what am I doing wrong.

"MySQL workbench then shows unexpected end and the en of the create statement." Could you edit this to quote the precise error, location, etc.? This phrasing isn't clear. — underscore_d

Bill Karwin Bill Karwin · Accepted Answer · 2017-11-22T21:42:30

https://dev.mysql.com/doc/refman/5.7/en/declare.html says:

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

(emphasis mine)

Like the following:

CREATE PROCEDURE getLast24Hours()
    BEGIN
        DECLARE crt_time VARCHAR(8);
        DECLARE counter INT;
        DECLARE currentTime DATETIME;

        CREATE TABLE hours (`Hour` VARCHAR(8) NOT NULL, `Value` INT NOT NULL DEFAULT 0, PRIMARY KEY(`Hour`));

Creating temporary table inside stored procedure

1 Answers