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

    • 0
    • 4
    • 3
    • 1
    • 0
    • 0
    • 0
    • 0
    • 261
    Comment on it

    Conditional Constructs - IF...ELSEIF..ELSE

    This construct is used to evaluate some complex conditions and based on the results execute different set of statements. The basic syntax is

    IF <conditions> THEN
         <operations to be performed>
    ELSEIF <conditions> THEN
         <operations to be performed>
    ELSE
         <operations to be performed>
    END IF;
    
    

    Here multiple conditions can be checked in ELSEIF. ELSE takes care of the default execution i.e in case no condition is satisfied then what to do.
    In IF, ELSE can be skipped if no default execution is required.

    For example the code snippet discussed for Searched CASE can be re-written using IF...ELSEIF..ELSE as :

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

    The output will be same.
    If no steps are to be executed in case none of the conditions are satisfied we can simply change the code to

    DELIMITER $$
    
    CREATE PROCEDURE `getRange`(num int)
    BEGIN
    
        IF num > 0 AND num <=99 THEN
            SELECT 'Tens';
        ELSEIF num >=100 AND num <=999 THEN
            SELECT 'Hundreds';  
        END IF;
    END$$
    
    DELIMITER ;
    

    Now

    call getRange(5000)

    Will not return any result.

    Which one is better ?

    So the question that comes into mind is, which one is better CASE or IF.
    CASE should be preferred if the condition check required is not too complex, as it is :

    • More readable
    • Efficient

    If should be used when :

    • Conditions are too complex to be managed in CASE
    • No default operation is required i.e. nothing is to be done if none of the values are satisfied.

    Previous
    Conditional Constructs - CASE

 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: