您可以使用 IFNULL。傳遞 experience 或 id_location 值並使用 NULL 作為另一個值。

為參數制定命名方案（此處為 in_-prefix）也是一個好習慣，以便參數與列名稱不同。

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 = 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 ;

例如，您可以使用這個：

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 = 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;

如果提供 IN 經驗 INT 設定為某個值，則套用它的條件。如果您為此參數提供 NULL，則套用 IN id_location INT 的條件。

注意 - 現在你的 SP 有 5 個參數，而不是 4 個。

PPS。使用類似的方法，您可以建立 SP，它可以同時套用其中一個條件，也可以同時套用這兩個條件，或者都不套用。例如，它可以是：

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

購買力平價。如果您想要有 2 個獨立的函數但有一份程式碼副本（例如，這些函數名稱已在一堆程式碼中使用），那麼您可以這樣做：

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);