mysql case statement

the mysql case statement makes the code more readable and efficient.

the case statements in MySQL can be divided into two form simple and searched.

MySQL case statement syntax

             case     case_expression

                      when    when_expression     then   commands
                                       
                      .....
                                       
                      else  commands
                  
             end  case;

or

             case     

                      when  condition_statement     then  commands
                                       
                      when  condition_statement     then  commands
                      
                      .....
                                       
                      else  commands
                  
             end  case;

you use the simple case statement to check the value of an expression against a set of unique values.

        delimiter$$
               
                create  procedure getshipping(
                        
                           in      u_customernumber   int(10),
                              
                           out   u_shipping               varchar(100))
                    
                begin
                    
                           declare  customercountry   varchar(30);
                                        
                           select   country  into  customercountry
                                  
                           from      customers
                                  
                           where    customernumber  = u_customernumber;
                                  
                             case    customercountry
                             
                                        when   'uae'    then
                                        
                                                    set     u_shipping = '3-day shipping';
                                                     
                                        when   'usa'    then
                                        
                                                    set     u_shipping = '4-day shipping';
                                                    
                                        else
                                        
                                                    set     u_shipping = '6-day shipping';             
                                                     
                 end case;
                              
         end$$ 

how it work

the getshipping stored procedure accepts customer number as an in parameter and returns shipping period based on the country of the customer.

"-Inside the stored procedure, first we get the country of the customer based on the input customer number.Then we use the simple CASE statement to compare the country of the customer to determine the shipping period.If the customer locates in UAe, the shipping period is 3-day shipping.If the customer is in usa, the shipping period is 4-day shipping.The customers from other countries have 6-day shipping".