Conditional Constructs - CASE

As in any programming constructs even in stored routines we might need to execute different commands based on what the variable or expression evaluates to. For the same we have CASE.... END CASE and IF...ELSEIF...ELSE constructs available in MYSQL.
In this part we will review where CASE will be useful and what variants do we have. Mainly CASE is used wherever we have to compare a variable/expression against a fixed set of values.
But in MYSQL we have a variant available for CASE, known as SEARCHED CASE its similar to simple CASE but instead of fixed set of values it can check for some complex conditions for example checking variable for range of values.
The basic construct of a CASE is something like :

CASE <variable>
    WHEN <Value 1> THEN
        <Statements to be executed>
    WHEN <Value 2> THEN
        <Statements to be executed>
    ELSE
        <Statements to be executed>
END CASE;

Here ELSE includes those statement which have to be executed in case none of the values matched. If no action is required then the ELSE should have BEGIN END; and the code will look something like :

CASE <variable>
    WHEN <Value 1> THEN
        <Statements to be executed>
    WHEN <Value 2> THEN
        <Statements to be executed>
    ELSE
        BEGIN END;
END CASE;

Lets check an example where we need to get country name from the abbreviation and in case abbreviation does not match it should return "No record found"

DELIMITER $$

CREATE PROCEDURE `getCountryName`(in abbrv varchar(20))
BEGIN
CASE abbrv
    WHEN 'IND' THEN
        SELECT 'India';
    WHEN 'USA' THEN
        SELECT 'United States of America';
    ELSE
        SELECT 'No record found';
END CASE;
END$$

DELIMITER ;

Here

CALL getCountryName('IND')

INDIA

CALL getCountryName('XYZ')

No record found

DELIMITER $$

CREATE PROCEDURE `getCountryName`(in abbrv varchar(20))
BEGIN
CASE abbrv
    WHEN 'IND' THEN
        SELECT 'India';
    WHEN 'USA' THEN
        SELECT 'United States of America';
    ELSE
        BEGIN END;
END CASE;
END$$

DELIMITER ;

In this scenario :

CALL getCountryName('XYZ')

Will not return anything.

SEARCHED CASE

DELIMITER $$

CREATE PROCEDURE `getRange`(num int)
BEGIN
CASE
    WHEN num > 0 AND num <=99 THEN
        SELECT 'Tens';
    WHEN num >=100 AND num <=999 THEN
        SELECT 'Hundreds';
    ELSE
        SELECT 'out of range';
END CASE;
END$$

DELIMITER ;

In the above example we are checking if the 'num' variable is between a range of values and depending on that we are performing the operations. So :

call getRange(50)

Will return

Tens

And :

call getRange(500)

Will return

Hundreds

In next blog we will discuss how IF...ELSEIF...ELSE can be used and which is better to use.