case statement in stored procedure

Question

I have this stored procedure, its working

CREATE PROCEDURE myrange()
BEGIN
SET @start = 0;
PREPARE stmt FROM ' SELECT firstname FROM peoples ORDER BY id LIMIT ?,1 INTO @name';
    WHILE @start < 5 DO
        EXECUTE stmt USING @start;
        CASE 
            WHEN @name = 'Ana'
                THEN INSERT INTO mytable (log) VALUES('yes');
            ELSE 
                INSERT INTO mytable (log) VALUES('no');
        END CASE;
        SET @start = @start + 1;
    END WHILE;
END;

but, if I delete from this procedure, this piece of code:

ELSE 
    INSERT INTO mytable (log) VALUES('no');

mysqli_error() returns this error: "Case not found for CASE statement".

Someone know, why?

score 3 · Accepted Answer · edited Jul 14 '12 at 12:38

3

From the MySQL CASE syntax

If no when_value or search_condition matches the value tested and the CASE statement contains no ELSE clause, a Case not found for CASE statement error results.

You must have all options covered.

The MySQL manual is a great documentation. Be sure to visit it!

edited Jul 14 '12 at 12:38

Lieven Keersmaekers

57,207
13
112
146

answered Jul 14 '12 at 12:16

Shlomi Noach

9,073
1
23
20

There are *so may* different things between vendors. You must always be sharp to recognize them. – Shlomi Noach Jul 14 '12 at 12:40
... and even version differences from one product. It seems that using MySQL 5.5.25 *does* allow it. – Lieven Keersmaekers Jul 14 '12 at 12:44
The 5.5 documentation repeats the same condition. Is this something you've just tested on a 5.5 server? – Shlomi Noach Jul 14 '12 at 12:49
1

I noticed that to but I tested this on [SQLFiddle](http://sqlfiddle.com/#!2/d41d8/1120). I assume the statement gets executed agains a MySQL database. So either the database where the statement gets executed on SQLFiddle is not a MySQL one or the documentation is not up-to-date. – Lieven Keersmaekers Jul 14 '12 at 12:50
Then perhaps you've actually used a value that is *found* by the case statement. Have just tried on **5.5.24** and it fails on `ERROR 1339 (20000): Case not found for CASE statement`. I did not test on **5.5.25** but I will be very much surprised to find that it works. – Shlomi Noach Jul 14 '12 at 12:59
You can follow the link to SQLFiddle to verify for yourself but I don't see anything wrong with it. According to the documentation, the statement should fail but on SQLFiddle, it returns two NULL values. The statement is equivalent to this `select case when t = 1 then 'x' end from (select 2 as t) as q`. Can you try this statement on a 5.5.24? – Lieven Keersmaekers Jul 14 '12 at 13:02
I now see your error. You have used the [case expression](http://dev.mysql.com/doc/refman/5.5/en/control-flow-functions.html#operator_case) instead of [case statement](http://dev.mysql.com/doc/refman/5.5/en/case-statement.html)... The two are not the same. The original question relates to the latter. – Shlomi Noach Jul 14 '12 at 13:12
Perhaps so but what about [this](http://sqlfiddle.com/#!2/d41d8/1125). This is a case expression and a searched case statement. Both of them work. – Lieven Keersmaekers Jul 14 '12 at 13:19
No, both these cases make for a case expression, not a case statement. A case statement is a construct in stored routine programming. A case expression, (both versions you used), is a query clause. – Shlomi Noach Jul 14 '12 at 13:27
Thank you Noach, the penny **finally** dropped. – Lieven Keersmaekers Jul 14 '12 at 13:44
1

Be kind to your forehead. It should serve you for years to come. Name's Shlomi. My pleasure – Shlomi Noach Jul 14 '12 at 13:50
Thanks very much Shlomi Noach and Lieven. – ოთო შავაძე Jul 14 '12 at 18:49

case statement in stored procedure

1 Answers1

Linked