Apply custom WHERE condition based on a parameter

Question

I have a stored procedure that contains a query with multiple conditions in the WHERE clause.

I can call this stored procedure with 2 different parameters. If I call it with 'X' parameter, I would like to apply a specific condition, however if I call it with 'Y' parameter, I would want to not apply that condition.

However, the remaining conditions in the where clause should apply to both 'X' and 'Y' parameters.

SELECT t.*
FROM tbl_1 t
WHERE 1 = 1
    -- I want these two conditions to apply to both 'X' and 'Y' parameters
    AND EXISTS (SELECT ..... WHERE value = @Param)
    AND NOT EXISTS (SELECT ..... WHERE value = @Param)

    --and lastly the third condition should only be applied if I have 'X' parameter when calling this procedure
    AND (@Param = 'X'
         AND t.ApptDt > '02/02/2023'
         AND t.CallDt > '02/1/2023')

This logic doesn't seem to work. If I call this with 'Y' parameter I get no values, unless I comment out this last bit.

How can I implement this type of logic?

Thanks.

Answer 1

Then try changing your LAST condition FROM

AND (@Param = 'X'
     AND t.ApptDt > '02/02/2023'
     AND t.CallDt > '02/1/2023')

to

AND (   @Param = 'Y'
     OR (@Param = 'X'
        AND t.ApptDt > '02/02/2023'
        AND t.CallDt > '02/1/2023')
    )