Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 

  • Stored Procedures and Functions in MySQL- Part 5

    @sukhminder.kaur
    Recommendations Offline Message

    • 0
    • 4
    • 3
    • 1
    • 0
    • 0
    • 0
    • 0
    • 322
    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 

    1. IF <conditions> THEN
    2.      <operations to be performed>
    3. ELSEIF <conditions> THEN
    4.      <operations to be performed>
    5. ELSE
    6.      <operations to be performed>
    7. 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 :

    1. DELIMITER $$
    2.  
    3. CREATE PROCEDURE `getRange`(num int)
    4. BEGIN
    5.  
    6.     IF num > 0 AND num <=99 THEN
    7.         SELECT 'Tens';
    8.     ELSEIF num >=100 AND num <=999 THEN
    9.         SELECT 'Hundreds';
    10.     ELSE
    11.         SELECT 'out of range';
    12.     END IF;
    13. END$$
    14.  
    15. 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

    1. DELIMITER $$
    2.  
    3. CREATE PROCEDURE `getRange`(num int)
    4. BEGIN
    5.  
    6.     IF num > 0 AND num <=99 THEN
    7.         SELECT 'Tens';
    8.     ELSEIF num >=100 AND num <=999 THEN
    9.         SELECT 'Hundreds';  
    10.     END IF;
    11. END$$
    12.  
    13. DELIMITER ;

    Now

    1. 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

    mysql
    Comment on it

 0 Comment(s)

Sign In
Forgot Password?
                           OR                           
Create an account !!
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Reset Password
Fill out the form below and reset your password: