MySQL Stored Procedures: CASE syntax

Newsletter Subscription | Glossary | Contact Us

Home > All Categories > MySQL > Education > MySQL Stored Procedures: CASE syntax

MySQL Stored Procedures: CASE syntax

Authored by: Thomas Darwin
Viewed: 986 times so far

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 searched case syntax simply chooses the first WHEN...THEN branch for which the condition appearing after the WHEN keyword is TRUE:

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

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!

Author : Roland Bouman

Click Here to View all the questions in Education category.

File Attachments

There are no attachment file(s) related to this question.