Sunday, 28 Sep 2008 7:18 EST
MySQL Stored Procedures: CASE Statements
Many developers aren't aware that there is a CASE statement in MySQL 5.0 which enables you to have if-then-else functionality directly in your SQL. The CASE statement is for stored procedures, which are SQL statements that can be stored on the server so that clients don't have to keep reissuing the same statements ove rand over. The functionality of the MySQL CASE statement is basically the same as any other case function in programming. If a search condition evaluates as true, then the following block of code is executed. You can have any number of if-then statements and follow them up with a single else statement to be executed if none of the search conditions are true. Be sure to always add an ELSE clause into your code block. MySQL will return the "Case not found for CASE statement" error if no search conditions evaluate to true and there is no ELSE clause. For instances where there is no ELSE code to execute, use an empty BEGIN . . . END code block. The CASE function is one of the most powerful functions in SQL coding. The CASE function can evaluate complex conditional statements, use any comparison operator and check multiple conditions all directly in your SQL code. CASE allows you to combine multiple SQL queries into one powerful query, based on the conditions you lay out.Let's take a look at the following example. Say we have a database of baseball statistics and we want to pull all batters statistics and group them by how good of a hitter they are.
DELIMITER //[1]
CREATE PROCEDURE battertype()
BEGIN
DECLARE batting_avg INT DEFAULT .000;
CASE batting_avg
WHEN batting_avg > .300 THEN 'Great Hitter'
WHEN batting_avg >= .275 and batting_avg < .300 THEN 'Good Hitter'
WHEN batting_avg >= .250 and batting_avg < .275 THEN 'Average Hitter'
WHEN batting_avg < .250 THEN 'Minor Leaguer'
ELSE
BEGIN
END;
END CASE;
END;
//
3 Comments So Far