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!
5 comments:
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
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,
Roland
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.
Why?
Thank You for Your response.
CODE
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[casewhen]
@id int=null,
@name varchar(50)=null,
@m1 int=null,
@m2 int=null,
@m3 int=null,
@class varchar(20)=null
as
begin
DECLARE @total dec(12,2)
SET @total = (@m1+@m2+@m3)
select
case @total
when @total <= 150
then
@class='second'
else
@class='first'
insert into Marks(m1,m2,m3,total,class) values(@m1,@m2,@m3,@total,@class)
end
//CODE
Ihave table marks
contain fields id,m1,m2,m3,tot,class
how can i display class like first,second,pass
using above query...
Thx....
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'
END
INSERT
INTO Marks (m1, m2, m3, total, class)
VALUES (@m1, @m3, @m3, @total, @class)
I hope this helps.
Post a Comment