Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Stored Procedures and Functions in MySQL- Part 4

    • 0
    • 3
    • 3
    • 1
    • 0
    • 0
    • 0
    • 0
    • 328
    Comment on it

    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')
    Will return
    INDIA
    whereas
    CALL getCountryName('XYZ')
    Will return
    No record found

    In case we dont want to execute any statement if the values don't match then the code will look something like :
    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

    Its same as CASE in functioning but instead of fixed set of values we can do a more complex check. The following example will be helpful in explaining the utility :
    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.


 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: