How to aggregate 2 procuderes into 1 in MySQL?

Question

I have two programs that look almost identical. One person receives location and price and performs the action, the other person receives experience and price. < /p>

First:

-- Returns: service providers in given location and price
DELIMITER &&  
CREATE PROCEDURE get_service_providers_location_price (IN id_location INT,IN price DOUBLE,IN limite INT, IN inicio INT)  
BEGIN  
    SELECT user.idUser, user.name,user.lastActivity,user.active,serviceprovider.description,location.name AS location, location.cordsX, location.cordsY, file.image FROM user
    INNER JOIN location ON user.idLocation = location.idLocation
    INNER JOIN file ON user.idUser = file.idUser
    INNER JOIN serviceprovider ON user.idUser = serviceprovider.idSP 
    INNER JOIN category_has_serviceprovider ON serviceprovider.idSP = category_has_serviceprovider.idServiceProvider 
        WHERE user.type = 3 AND user.idLocation = id_location  
            AND (category_has_serviceprovider.price <= price OR category_has_serviceprovider.price IS NULL) and serviceprovider.idSubscription != 1 
    ORDER BY CASE WHEN serviceprovider.idSubscription in (5,6,7) then 1 else 2 end, serviceprovider.endSub ASC LIMIT limite OFFSET inicio;
END &&  
DELIMITER ;

the second:

-- Returns: service providers in given experience and price
DELIMITER &&  
CREATE PROCEDURE get_service_providers_experience_price (IN experience INT, IN price DOUBLE,IN limite INT, IN inicio INT)  
BEGIN  
    SELECT user.idUser, user.name,user.lastActivity,user.active,serviceprovider.description,location.name AS location, location.cordsX, location.cordsY, file.image FROM user
    INNER JOIN location ON user.idLocation = location.idLocation
    INNER JOIN file ON user.idUser = file.idUser
    INNER JOIN serviceprovider ON user.idUser = serviceprovider.idSP 
    INNER JOIN category_has_serviceprovider ON serviceprovider.idSP = category_has_serviceprovider.idServiceProvider 
        WHERE user.type = 3
            AND (category_has_serviceprovider.price <= price OR category_has_serviceprovider.price IS NULL) AND category_has_serviceprovider.experience >= experience and serviceprovider.idSubscription != 1 
    ORDER BY CASE WHEN serviceprovider.idSubscription in (5,6,7) then 1 else 2 end, serviceprovider.endSub ASC LIMIT limite OFFSET inicio;
END &&  
DELIMITER ;

As you can see, only the WHERE clause has changed. In MySQL, is it possible to aggregate these two procedures into one? Because I have about 5 programs that look the same but it just changes WHERE clause, I find it annoying to do a separate procedure for each case.

Answer 1

You can use IFNULL. Pass the experience or id_location value and use NULL for the other value.

It is also a good practice to have a naming scheme (here in_-prefix) for the parameters so that the parameters are distinct from the column names.

DELIMITER &&  
CREATE PROCEDURE get_service_providers_experience_price (
in_experience INT, 
in_id_location INT, 
in_price DOUBLE,
in_limite INT, 
in_inicio INT
)  
BEGIN  
SELECT 
  user.idUser, 
  user.name,
  user.lastActivity,
  user.active,
  serviceprovider.description,
  location.name AS location, 
  location.cordsX, 
  location.cordsY, 
  file.image 
FROM user
  INNER JOIN location ON user.idLocation = location.idLocation
  INNER JOIN file ON user.idUser = file.idUser
  INNER JOIN serviceprovider ON user.idUser = serviceprovider.idSP 
  INNER JOIN category_has_serviceprovider ON serviceprovider.idSP = category_has_serviceprovider.idServiceProvider 
WHERE user.type = 3
  AND (category_has_serviceprovider.price <= in_price OR category_has_serviceprovider.price IS NULL) 
  AND category_has_serviceprovider.experience >= IFNULL(in_experience, category_has_serviceprovider.experience)
  AND user.idLocation = IFNULL(id_location, user.idLocation)
  AND serviceprovider.idSubscription != 1 
ORDER BY CASE WHEN serviceprovider.idSubscription in (5,6,7) then 1 else 2 end, serviceprovider.endSub ASC 
LIMIT in_limite OFFSET in_inicio;

END 
&&  
DELIMITER ;

Answer 2

For example, you can use this:

CREATE PROCEDURE get_service_providers_price (IN experience INT,IN id_location INT,IN price DOUBLE,IN limite INT, IN inicio INT)  
BEGIN  
SELECT user.idUser, user.name,user.lastActivity,user.active,serviceprovider.description,location.name AS location, location.cordsX, location.cordsY, file.image FROM user
INNER JOIN location ON user.idLocation = location.idLocation
INNER JOIN file ON user.idUser = file.idUser
INNER JOIN serviceprovider ON user.idUser = serviceprovider.idSP 
INNER JOIN category_has_serviceprovider ON serviceprovider.idSP = category_has_serviceprovider.idServiceProvider 
WHERE user.type = 3 
  AND (category_has_serviceprovider.price <= price OR category_has_serviceprovider.price IS NULL) 
  and serviceprovider.idSubscription != 1 
  AND CASE WHEN experience IS NOT NULL
           THEN category_has_serviceprovider.experience >= experience
           ELSE user.idLocation = id_location  
           END
ORDER BY CASE WHEN serviceprovider.idSubscription in (5,6,7) then 1 else 2 end, serviceprovider.endSub ASC LIMIT limite OFFSET inicio;

If the provided IN experience INT is set to a value, its condition is applied. If you provide NULL for this parameter, the condition for IN id_location INT applies.

NOTE - Your SP now has 5 parameters instead of 4.

PS. Your SP contains a single SQL statement - so BEGIN-END and DELIMITER are not needed.

PPS. Using a similar approach, you can create an SP that applies one, both, or neither of the conditions. For example, it could be:

AND CASE WHEN experience IS NOT NULL AND id_location IS NOT NULL  -- apply both parameters filtering
         THEN category_has_serviceprovider.experience >= experience AND user.idLocation = id_location
         WHEN experience IS NOT NULL   -- apply filtering by experience  only
         THEN category_has_serviceprovider.experience >= experience
         WHEN id_location IS NOT NULL   -- apply filtering by location only
         THEN user.idLocation = id_location
         ELSE 1    -- not filter, return all rows
         END

Purchasing Power Parity. If you want to have 2 separate functions but have a copy of the code (for example, these function names are already used in a bunch of code), then you can do this:

CREATE PROCEDURE get_service_providers_location_price (IN id_location INT,IN price DOUBLE,IN limite INT, IN inicio INT)
CALL get_service_providers_price (NULL, id_location, price, limite, inicio);

CREATE PROCEDURE get_service_providers_experience_price (IN experience INT, IN price DOUBLE,IN limite INT, IN inicio INT)
CALL get_service_providers_price (experience, NULL, price, limite, inicio);