CODEWITHSUNDEEP
mysqlstored-procedureswhile-loopprepared-statement
ოთო შავაძე
ოთო შავაძე

Reputation: 1045

PREPARED STATEMENT in a WHILE loop

I have a table autos that has a column name, I want to check first 5 rows in the table and if name value is "toyota", in table mytable write "yes", else write "no". I write stored procedure, but mysqli_error() returns error in line, where I have EXECUTE ....

If in WHEN I write not PREPARED STATEMENT, but directly the query, the procedure works. Please see my code and tell me, where is it wrong?

CREATE PROCEDURE proc_auto()
BEGIN

DECLARE start INT;
SET start = 0;
PREPARE stmt FROM  ' SELECT name FROM autos ORDER BY id LIMIT ?,1 ';

WHILE start < 5 DO

    CASE 
        WHEN (EXECUTE stmt USING @start ) = 'toyota'
            THEN INSERT INTO mytable (log)  VALUES('yes');
        ELSE 
           INSERT INTO mytable (log)  VALUES('no');
    END CASE;
    SET start = start + 1;

END WHILE;
END;

Upvotes: 1

Views: 3069

Answers (2)

Ross Smith II
Ross Smith II

Reputation: 12189

Andriy M's INSERT statement is the most elegant solution, but if you still want to use a procedure, this will work:

CREATE PROCEDURE proc_auto()
BEGIN
    DECLARE start INT DEFAULT 0;

    PREPARE stmt FROM 
        'SELECT name INTO @name FROM autos ORDER BY id LIMIT ?,1';

    WHILE start < 5 DO
        SET @start = start;
        EXECUTE stmt USING @start;
        IF @name = 'toyota' THEN
            INSERT INTO mytable (log)  VALUES('yes');
        ELSE 
            INSERT INTO mytable (log)  VALUES('no');
        END IF;
        SET start = start + 1;
    END WHILE;
END;

but, in this case, using a CURSOR would yield better performance:

CREATE PROCEDURE proc_auto()
BEGIN
    DECLARE start INT DEFAULT 0;
    DECLARE b_not_found BOOL DEFAULT FALSE;

    DECLARE cur CURSOR FOR
        'SELECT name FROM autos ORDER BY id LIMIT 5';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b_not_found = TRUE;

    OPEN cur;

    loop1: WHILE start < 5 DO
        FETCH cur INTO @name;
        IF b_not_found THEN
            LEAVE loop1;
        END IF;

        IF @name = 'toyota' THEN
            INSERT INTO mytable (log)  VALUES('yes');
        ELSE 
            INSERT INTO mytable (log)  VALUES('no');
        END IF;
        SET start = start + 1;
    END WHILE;

    CLOSE cur;
END;

Upvotes: 2

Andriy M
Andriy M

Reputation: 77737

(The suggestion about EXECUTE is removed as incorrect and potentially confusing.)

The problem you are trying to solve with a stored procedure could in fact be solved without it, using an entirely different approach: just use a single INSERT ... SELECT statement instead:

INSERT INTO mytable (log)
SELECT
  CASE name
    WHEN 'toyota' THEN 'yes'
    ELSE 'no'
  END
FROM autos
ORDER BY id
LIMIT 5

That is, the above statement does the same as your stored procedure: it retrieves first 5 rows from autos and inserts 5 rows into mytable. Depending on the value of name it generates either yeses or nos.

Upvotes: 2

Related Questions