Search code examples
mysqldatabasestored-proceduresdatabase-designmysql-logic

MYSQL Stored Procedure Query Fallback Logic

I am sure this is a stupid question but after reading everything I cannot figure out the proper way of creating a stored procedure with fallback logic for the Where clause.

I have the following query:

SELECT FeeName, Amount, Category, VariableName 
FROM client_rates WHERE state_abv = state_abvIN 
AND Purchase= purchaseIN AND Category= CategoryIN And client_id=client_idIN AND
agent_id=agent_idIN AND ((lender_id=lender_idIN AND county=countyIN) OR (lender_id=lender_idIN
AND
county='NA') OR (lender_id=1 AND county=countyIN) OR (lender_id=1 AND county='NA'));

Because I have used OR it will return a set which matches all 4 conditions. What I want is to "fallback" through conditions.

Meaning I want it to return a set where (lender_id=lender_idIN AND county=countyIN) returns a value. However if it returns a NULL set then try the next condition set (lender_id=lender_idIN AND county='NA') and etc.

I dont think CASE will work for this issue and I once used nested IF EXIST statements which worked but forced each query to run twice (there has to be a better solution than that).

Any suggestions and thank you for the help.

Solution

  • Your question is not clear. I'll assume that the ...IN symbols are parameters not columns, that 'NA' is a possible value for county in client_rates, and that in your WHERE's last AND condition you want to return rows where (...), otherwise where (...), otherwise where (...), etc. (A CASE expression evaluates only up to the first true WHEN.)

    SELECT FeeName, Amount, Category, VariableName 
FROM client_rates
WHERE state_abv=state_abvIN 
AND Purchase=purchaseIN AND Category=CategoryIN
AND client_id=client_idIN AND agent_id=agent_idIN
AND CASE WHEN (lender_id=lender_idIN AND county=countyIN) THEN 1
    WHEN (lender_id=lender_idIN AND county='NA') THEN 1
    WHEN (lender_id=1 AND county=countyIN) THEN 1
    ELSE (lender_id=1 AND county='NA')
    END;

    PS: But that is a different query from the following: When there is a match for everything before the CASE, if lender_idIN is not found then 1 is returned for lender_id (whether or not there is a lender_id=1) and if countyIN is not found then 'NA' is returned for county (whether or not there is a county='NA'). (This probably involves 'NA' not being a possible value for county in client_rates.) When they give different results: If lender_idIN is not found but there is no client_rates lender_id value of 1 and/or if countyIN is not found but there is no client_rates county value of 'NA'. Then the first query will return an empty table but the second query returns a 1 for lender_id and/or a 'NA' for county. Make your "Meaning..." clear! Include the query "which worked" (if you're sure it did).