MySQL Stored Procedures: CASE syntax

Thank you all for taking the time to respond to the little challenge I posted yesterday! I am pleasantly surprised to note that so many people took the time to post a solution. And most people provided the correct answer too: you are all entitled to a well deserved discount to register for the MySQL User's conference!!!

For those of you interested in the solution: there are two different forms of the CASE statement syntax: the so-called simple case and the searched case.

The simple case selects one WHEN...THEN branch by comparing the value of the expression that appears after the CASE keyword with the value of the expressions given in each of the WHEN...THEN branches. It enters the first branch found where the values are equal to one another:

CASE expr
    WHEN expr1 THEN ...statements...
    ...    
    WHEN exprN THEN ...statements...
    ELSE ...statements...
END CASE

The searched case syntax simply chooses the first WHEN...THEN branch for which the condition appearing after the WHEN keyword is TRUE:

CASE 
    WHEN cond1 THEN ...statements...
    ...    
    WHEN condN THEN ...statements...
    ELSE ...statements...
END CASE

The p_find_slash procedure uses a simple case but accidentally used conditions the WHEN...THEN branches:

CASE v_char
    WHEN v_char = '/' THEN ...
    ...
END CASE

What many people don't realize is that syntactically this is perfectly valid. That's because to MySQL, those conditions are simply particular types of expression. It's just that their value will be either 0 or 1 depending on whether the condition holds FALSE or TRUE respectively (consequently, the built-in constants FALSE and TRUE are in fact synonyms for 0 and 1 respectively).

I already emailed a few of you, so if you didn't yet receive an email from me, send me your email address and I'll make sure you get the discount code. I can be reached via email:

Roland dot Bouman at gmail dot com

See you at the UC!

MySQL stored procedurs: ...the CASE that they gave me...

Let's see if you can solve this little puzzle...

Consider this stored procedure:

-- finds the first slash and exits
create procedure p_find_slash(p_text text)
begin
    declare v_index int default 1;
    declare v_length int default character_length(p_text);
    declare v_char char(1);
    
    _main_loop: while v_index <= v_length do                       -- loop over all characters
                
        set v_char := substring(p_text, v_index, 1);               -- grab the current character
        case v_char
            when v_char = '/' then                                 -- found a slash!                
                select concat('A slash at ', v_index) message;     -- report it
                leave _main_loop;                                  -- and then stop
            else                                                   
                select concat(v_char, ' is not a slash.') message; -- not a slash...               
        end case;        
        set v_index := v_index + 1;                                -- next character pls
    end while;
end;

Of course, it's a bogus stored procedure, but that's not the point right now. Most people can guess what the intention is of this procedure: it should examine each character in the argument text, check if it is a slash, and if so, report its position and then stop execution. If the character is not a slash, the procedure moves on to the next character.

So, what do you expect when we call it with only a literal slash as argument? Let's find out:

call p_find_slash('/');

Well? It may come as a surprise to some, but this is the result:

+-------------------+
| message           |
+-------------------+
| / is not a slash. |
+-------------------+

So, can you explain this result? Can you fix it?

Just leave a comment to this post with your explanation and the solution. Results published later this week....and oh!! If you know how to fix this, maybe you're ready to move on to the next level and should attend the "Advanced Stored Procedures" tutorial by Mariella Di Giacomothe at the MySQL User's Conference. Like Giuseppe Maxia wrote earlier, you can earn a 20% discount code by asking a speaker!!

So that is the return: if you post your solution as a comment on this blog, I'll make sure you'll get that code for a 20% discount.