DELIMITER $$

DROP PROCEDURE IF EXISTS GetCustomerSearchResult$$

CREATE DEFINER=root@% PROCEDURE GetCustomerSearchResult(

    criteria INT
,searchValue VARCHAR(500)    
,MinLimit INT
,MaxLimit INT 

)

BEGIN

CASE

    WHEN criteria=1 /*search by CustomerName*/
    THEN    
        PREPARE STMT FROM 
        " SELECT DISTINCT c.CustomerName AS CustomerName , c.FatherName AS FatherName, 
        DATE_FORMAT(c.DOB,'%d-%m-%Y') AS DOB, DATE_FORMAT(cm.ActivationDate,'%d-%m-%Y') AS ActivationDate,
        cm.MobileNumber AS MobileNumber,cm.Address FROM Customer AS c INNER JOIN Customer_mobile_data  AS cm 
        ON c.Id=cm.CustomerId WHERE CustomerName LIKE CONCAT(?,'%') LIMIT ? , ?"; 
        SET @searchValue = searchValue; 
        SET @START = MinLimit; 
        SET @LIMIT = MaxLimit; 
        EXECUTE STMT USING @searchValue, @START, @LIMIT;
        DEALLOCATE PREPARE STMT;

    WHEN criteria=2 /*search by MobileNumber*/
    THEN    
        PREPARE STMT FROM 
        "SELECT DISTINCT c.CustomerName AS CustomerName , c.FatherName AS FatherName, 
        DATE_FORMAT(c.DOB,'%d-%m-%Y') AS DOB, DATE_FORMAT(cm.ActivationDate,'%d-%m-%Y') AS ActivationDate,
        cm.MobileNumber AS MobileNumber,cm.Address FROM Customer AS c INNER JOIN Customer_mobile_data  AS cm 
        ON c.Id=cm.CustomerId WHERE MobileNumber LIKE CONCAT(?,'%') LIMIT ? , ?"; 
        SET @searchValue = searchValue; 
        SET @START = MinLimit; 
        SET @LIMIT = MaxLimit; 
        EXECUTE STMT USING @searchValue, @START, @LIMIT;
        DEALLOCATE PREPARE STMT;


    ELSE /*search all*/
        PREPARE STMT FROM 
        "SELECT DISTINCT c.CustomerName AS CustomerName , c.FatherName AS FatherName, 
        DATE_FORMAT(c.DOB,'%d-%m-%Y') AS DOB, DATE_FORMAT(cm.ActivationDate,'%d-%m-%Y') AS ActivationDate,
        cm.MobileNumber AS MobileNumber,cm.Address FROM Customer AS c INNER JOIN Customer_mobile_data  AS cm 
        ON c.Id=cm.CustomerId LIMIT ? , ?"; 
        SET @START = MinLimit; 
        SET @LIMIT = MaxLimit; 
        EXECUTE STMT USING @START, @LIMIT;
        DEALLOCATE PREPARE STMT;
END CASE;  
END$$

DELIMITER ;