ตอนที่ 10 : การใช้ CASE WHEN บน Stored Procedure (MySQL : Stored Procedure)

CASE  case_expression
   WHEN expression_1 THEN 
      # Statement 1
   WHEN expression_2 THEN
      # Statement 2
   ELSE
      # Statement 3
END CASE;

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer(IN pType VARCHAR(1))
BEGIN
	
	CASE pType
	WHEN "1" THEN
		SELECT A.CUSTOMER_ID, A.NAME, A.EMAIL, B.COUNTRY_NAME FROM customer A 
			LEFT JOIN country B ON  A.COUNTRY_CODE = B.COUNTRY_CODE 
				WHERE A.COUNTRY_CODE = 'TH';
	WHEN "2" THEN
		SELECT A.CUSTOMER_ID, A.NAME, A.EMAIL, B.COUNTRY_NAME FROM customer A 
			LEFT JOIN country B ON  A.COUNTRY_CODE = B.COUNTRY_CODE 
				WHERE A.COUNTRY_CODE = 'US';
	ELSE
		SELECT A.CUSTOMER_ID, A.NAME, A.EMAIL, B.COUNTRY_NAME FROM customer A 
			LEFT JOIN country B ON  A.COUNTRY_CODE = B.COUNTRY_CODE 
				WHERE A.COUNTRY_CODE = 'UK';
	END CASE;

END //
DELIMITER ;

CALL getCustomer('1');