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;



                      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.

                create  procedure getshipping(
                           in      u_customernumber   int(10),
                           out   u_shipping               varchar(100))
                           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';
                                                    set     u_shipping = '6-day shipping';             
                 end case;

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