Using multiple nested OR statements in WHERE clause makes query return incorrect results

I have a WHERE clause that has a nested OR statement, as seen here:

-- Declaration of variables
DECLARE 
@PageSize INT, 
@PageNumber INT, 
@SearchPhraseOne VARCHAR(20), 
@SearchPhraseTwo VARCHAR(20), 
@FilterCategory VARCHAR(30), 
@FilterStatus TINYINT,
@NeedsFollowUp TINYINT,
@NeedsTraining TINYINT,
@NeedsInitialVacc TINYINT;

SET @PageNumber = 1;
SET @PageSize = 100;
SET @SearchPhraseOne = null;
SET @SearchPhraseTwo = null;
SET @FilterCategory = 'High Exposure';
SET @FilterStatus = null;
SET @NeedsFollowUp = 1;
SET @NeedsTraining = null;
SET @NeedsInitialVacc = null;

select * from(
select
    vel.fullName, 
    vel.EecEmpNo, 
    vel.EecLocation,
    vel.EecDateOfLastHire,
    job.JbcDesc,
    vei.eiInitialBBPDate, 
    vei.eiVCGivenDate,
    iif(jv.verTypeName is null, 'Low Risk', jv.verTypeName) as vaccineCategory, 
    vel.eecEmplStatus,
    count(distinct vh.vhID) as vaccCount,
    max(isnull(vh.vhNextDateScheduled, null)) as maxNextDateScheduled,
    max(cast(vh.vhSeriesComplete as int)) as seriesComplete,
    iif(vel.eecEmplStatus = 'T', null, 
    coalesce(iif(max(cast(vh.vhSeriesComplete as int)) = 1, null, max(isnull(vh.vhNextDateScheduled, null))), -- check if the vaccine items have a SeriesComplete of 1, otherwise use NextDateScheduled
            iif(vei.eiInitialBBPDate is not null, null, vel.EecDateOfLastHire), -- check if the InitialBBPDate is not null, if it isn't use DateOfLastHire
            iif(vei.eiVCGivenDate is not null, null, vel.EecDateOfLastHire), null)) as actionDate -- check if the OrientationDate is not null, if it isn't use DateOfLastHire
            -- if all three of these values are null then there's no ActionDate
            -- Terminated employees will not have an action date assigned even if there's a match
from dbo.vaccEmpList vel
    left join dbo.vaccEmployeeInfo vei on vei.eiEmployeeNo = vel.EecEmpNo
    left join dbo.vaccVaccinationHistory vh on vh.vhEmployeeNo = vel.EecEmpNo
    left join dbo.vaccVaccineTypeLookup vt on vh.vhVaccinationTypeID = vt.vtlVaccineTypeID and vt.vtIsActive = 1 -- Only get active vaccination types
    join dbo.U_JobCode job on vel.EecJobCode = job.JbcJobCode
    left join dbo.JobVerficationXref jv on vel.EecJobCode = jv.JobCode and jv.verName = 'Vaccination Category'
group by vel.fullName, vel.EecEmpNo, job.JbcDesc, jv.verTypeName, vel.EecLocation, vel.eecEmplStatus, vei.eiInitialBBPDate, vei.eiVCGivenDate, vel.EecDateOfLastHire
) as searchResults
where (
    (
        @SearchPhraseOne is null
        or searchResults.fullName like @SearchPhraseOne + '%'
        or searchResults.EecEmpNo = @SearchPhraseOne
    )
    and (
        @SearchPhraseTwo is null 
        or searchResults.fullName like @SearchPhraseTwo + '%' 
        or searchResults.EecEmpNo = @SearchPhraseTwo
    ) -- Employee Name/ID
    and (
        @FilterStatus is null
        or (searchResults.eecEmplStatus = 'A' or searchResults.eecEmplStatus = 'L')
    ) -- Employee Status
    and (
        @FilterCategory is null
        or searchResults.vaccineCategory = @FilterCategory
    ) -- Employee Vaccination Category
    and ( -- ISSUES OCCUR HERE
        (@NeedsTraining is null
            or (searchResults.actionDate is not null
                and (searchResults.eiInitialBBPDate is null or searchResults.eiVCGivenDate is null))
        )   -- Needs Training if either of these two date values are null
        or (@NeedsInitialVacc is null
            or (searchResults.actionDate is not null
                and (searchResults.vaccCount = 0)) 
                -- Needs Initial Vaccination if there are no vaccine records
        )
        or (@NeedsFollowUp is null
            or (searchResults.actionDate is not null
                and ((searchResults.seriesComplete is null or searchResults.seriesComplete = 0) and searchResults.maxNextDateScheduled is not null)) 
                -- Needs a follow-up date if no series complete was detected
        )
    )
)

The @NeedsFollowUp, @NeedsInitialVacc, and @NeedsTraining variables are all set by the variables above. When one or more of these are set to “1”, the query should return employee entries that match the criteria inside their related statements. For example, if the “NeedsFollowUp” and “NeedsTraining” values are set to “1” then the query should return employees that need a follow-up or employees that need training.

Right now, when I set all three to “1” I receive the combined results I’m looking for, but if any of them are set to null, then the query doesn’t return the correct results.

EDIT: Here’s a reproducible example of what I’m seeing.

I think the way the clauses are set up is causing an issue, but I’m not really sure how to fix this. How can I get the OR statements to work in the way I described above?

Answer

I was able to make the OR clauses work correcting by switching from is null to is not null in my where clauses. Using the minimal example, it would look like this:

select * from AGENTS
where (
    (@NeedsName is not null and AGENTS.AGENT_NAME is null)
    or
    (@NeedsCountry is not null and AGENTS.COUNTRY is null)
    or
    (@NeedsCountry is null and @NeedsName is null)
)

Be sure to include an additional clause for when all options are NULL, so that you can return the appropriate number of rows.

Here’s a working version.