Thursday, March 13, 2008

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...

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

WHEN cond1 THEN ...statements...
WHEN condN THEN ...statements...
ELSE ...statements...

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

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

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!


Anonymous said...

Dear Roland Bouman

You're greatfull in MySQL database.
I always see you in MySQL forum.

Can I ask you some questions.

Here's the question:
1. I'm using MySQL as my database server. I combine MySQL with Visual Basic as my program application.
I usualy using stored procedure as database transaction from my application.
Why when i execute the procedure from my application somtimes appears error like "Commands out of sync; you can't run this command now". Why this happend?
2. What is MyIsam or InnoDB mean in MySQL?

Thank you very much for you help

rpbouman said...

Hi Heng,

"Why when i execute the procedure from my application somtimes appears error like "Commands out of sync; you can't run this command now". Why this happend?"

This would indicate you ar calling the ADO or ODBC functions in the wrong order. I would have o see the code to say anything meaningful about it.

"What is MyIsam or InnoDB mean in MySQL?"

Those are the names of so called "storage engines". MySQL tables use a storage engine as back-end to take care of the actual physical storage, and you can choose on fit for your particular purpose.

PLease refer to the storage engine chapter in the reference manual to learn more.

Kind regards,


Unknown said...

Helo Bouman

You reply so fast

I don't know what codes i have to show you. Because there is no something error in my code.

The error appears like unstable condition. I've tried to install in two computer. One can run normally and the other have error.

There is any solution?

Another question
How to convert database type from datetime to varchar? and decimal to varchar?
Can I use this code?
CAST(now() as varchar(20))

I've error "Multiple step operation generated errors. Check each status value" when VB application catch result in DATE_FORMAT() function.
For example:
SELECT DATE_FORMAT(now(),'%d-%m-%Y');

In VB return "?????" value.

Thank You for Your response.

Anonymous said...


ALTER procedure [dbo].[casewhen]

@id int=null,
@name varchar(50)=null,
@m1 int=null,
@m2 int=null,
@m3 int=null,
@class varchar(20)=null


DECLARE @total dec(12,2)
SET @total = (@m1+@m2+@m3)


case @total
when @total <= 150
insert into Marks(m1,m2,m3,total,class) values(@m1,@m2,@m3,@total,@class)


Ihave table marks
contain fields id,m1,m2,m3,tot,class

how can i display class like first,second,pass
using above query...


rpbouman said...

Anonymous, this looks like MS SQL server code.

I'm not 100% sure, but I think MS SQL only supports the CASE expression, not the CASE statement. This means you should write it like so:

SET @class = CASE @total
WHEN @total <= 150 THEN 'second'
ELSE 'first'

INTO Marks (m1, m2, m3, total, class)
VALUES (@m1, @m3, @m3, @total, @class)

I hope this helps.


Nowadays, many SQL implementations offer some form of aggregate string concatenation function. Being an aggregate function, it has the effe...