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.
You can use IFNULL. Pass the
experience
orid_location
value and useNULL
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.For example, you can use this:
If the provided
IN experience INT
is set to a value, its condition is applied. If you provide NULL for this parameter, the condition forIN 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:
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: