CODEWITHSUNDEEP
mysqlstored-procedurestemp-tables
jose
jose

Reputation: 2543

MySQL procedure temporary table Select

I'm trying to create a temporary table and select results from it, inside a mysql procedure.

Here's a piece of the SQL

DELIMITER $$

CREATE PROCEDURE `mydb`.`simulate_results` (currdate DATETIME, enddate DATETIME, idcam INT)
BEGIN

DECLARE MYVAR1,
        MYVAR2,
        MYVAR3,
        IDX INT;

CREATE TEMPORARY TABLE IF NOT EXISTS tmp (
    `Id` INT NOT NULL AUTO_INCREMENT,
    `Field1` INT NOT NULL,
    `Field2` INT NOT NULL,
    `Field3` INT NOT NULL, PRIMARY KEY (`Id`)) ENGINE=MEMORY; 

INSERT INTO tmp (`Field1`,`Field2`,`Field3`) VALUES (0,0,0);
INSERT INTO tmp (`Field1`,`Field2`,`Field3`) VALUES (1,0,0);



SELECT Field1,Field2,Field3,Id INTO MYVAR1,MYVAR2,MYVAR3,IDXFROM tmp ORDER BY RAND() LIMIT 1;


        SELECT MYVAR1; (...)

The only variable that comes filled from the SELECT INTO statemente is IDX (primary key). The others are always NULL.

Is there something wrong with this code?

Upvotes: 1

Views: 6537

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179442

Have you looked at your temporary table to see what kind of data is lingering in it? The temporary table will still be there after the procedure completes and if you run the procedure again from the same session, you'll be writing new rows into the existing temporary table.

I would add DROP TEMPORARY TABLE IF EXISTS tmp before CREATE TEMPORARY TABLE ... to be sure you don't have old data hanging around.

Upvotes: 4

Related Questions