Temporary table definition in MySQL

Question

I have a stored procedure which uses temporary tables so that I can summarize the sales of all the products within a certain product category. When I tried to run the code it failed. I search on google and here on stackoverflow but couldn't find what I had done wrong. I'm using MySQL server 5.5 on Windows Server.

CREATE PROCEDURE `getStatistics`(IN `startDate` date,IN `endDate` date,IN  `categoryName` varchar)
BEGIN
    CREATE TEMPORARY TABLE procResult(productName VARCHAR, amount INT);
    CREATE TEMPORARY TABLE tblProductID(SELECT ID, `name` FROM product WHERE categoryID = (SELECT ID FROM category WHERE `name` = categoryName));
    DECLARE done_amt, done_PID INT DEFAULT FALSE;
    DECLARE amount, productID INT DEFAULT 0;
    DECLARE pidCursor CURSOR FOR SELECT ID, `name` FROM tblProductID;
    DECLARE amtCursor CURSOR FOR SELECT orderlines.amount FROM orderlines WHERE orderlines.productID = productID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_amt = TRUE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_PID = TRUE;

    OPEN pidCursor;

    pid_loop:LOOP
        DECLARE productName VARCHAR;
        FETCH pidCursor INTO productID, productName;

        IF done_PID THEN
            LEAVE pid_LOOP;
        END IF;

        OPEN amtCursor;

        amt_loop:LOOP
            DECLARE tmpAmount INT DEFAULT 0;
            FETCH amtCursor INTO tmpAmount;

            IF done_amt THEN
                LEAVE amt_loop;
            END IF;

            amount = amount + tmpAmount;
        END LOOP;

        CLOSE amtCursor;

        IF amount > 0 THEN
            INSERT INTO procResult VALUES (productName, amount);
            amount = 0;
        END IF;
    END LOOP;

    CLOSE pidCursor;
END;

Answer 1

1. You must define the length of VARCHAR type variables, such as the categoryName parameter to your stored procedure;

2. You must DECLARE all local variables at the very start of a BEGIN ... END compound statement block, before any other commands;

3. Your syntax for CREATE TABLE ... SELECT is incorrect;

4. You have declared two handlers for the same SQL condition, only one of which will be executed (indeterminately);

5. You will need to change your client's statement delimiter in order for it to understand that the semicolons appearing within the procedure body do not terminate the CREATE PROCEDURE statement;

6. Your entire procedure is an extremely complicated way of doing a fairly simple task in SQL:

   CREATE TEMPORARY TABLE procResult
     SELECT   product.name, SUM(orderlines.amount) AS amount
     FROM     orderlines
         JOIN product  ON  product.ID = orderlines.productID
         JOIN category ON category.ID = product.categoryID
     WHERE    category.name = ?
     GROUP BY product.ID
     HAVING   amount > 0