Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to search records with maximum and minimum limit in MySQL

    • 0
    • 2
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 107
    Comment on it

    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 ;

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: