基于三个bit列的视图过滤存储过程逻辑修正需求

Alright, let's fix that stored procedure's filtering logic to match your requirements. The original WHERE clause has a flawed structure that's not handling the OR/AND combinations correctly, plus it's not properly excluding NoImpact = 1 records when Profit or Loss are selected without the NoImpact checkbox ticked.

Here's the corrected WHERE clause, with logic that aligns perfectly with your test cases and rules:

WHERE 
    -- Match any of the selected checkboxes (OR relationship for multiple selections)
    (
        (@ProfitSelected = 1 AND Profit = 1)
        OR (@LossSelected = 1 AND Loss = 1)
        OR (@NoImpactSelected = 1 AND NoImpact = 1)
    )
    -- Exclude NoImpact=1 records only when NoImpact isn't selected, and at least one of Profit/Loss is selected
    AND (
        @NoImpactSelected = 1
        OR (@ProfitSelected = 0 AND @LossSelected = 0)
        OR NoImpact = 0
    )

How this works: #

1. First block: Ensures we only include records that match at least one of the ticked checkboxes. If you select both Profit and Loss, it pulls all records where Profit=1 OR Loss=1. If you select all three, it includes any record that meets any of the three conditions.

2. Second block: Handles the exclusion rule for NoImpact=1 records:

   • If you've ticked NoImpact, we don't exclude anything (this condition evaluates to true).
   • If you haven't ticked either Profit or Loss (meaning you only have NoImpact ticked, or none), we also don't apply exclusion.
   • For all other cases (you've ticked Profit/Loss but not NoImpact), we force NoImpact=0 to exclude those records.

Let's verify against your test cases: #

Test Case 1: exec dbo.SearchErrorReports @ProfitSelected = 1, @LossSelected = 1, @NoImpactSelected = 0

• First block: (Profit=1 OR Loss=1 OR false) → Profit=1 OR Loss=1
• Second block: (false OR (false AND false) OR NoImpact=0) → NoImpact=0
• Combined: (Profit=1 OR Loss=1) AND NoImpact=0 → Exactly matches your expected results.

Test Case 2: exec dbo.SearchErrorReports @ProfitSelected = 0, @LossSelected = 1, @NoImpactSelected = 0

• First block: (false OR Loss=1 OR false) → Loss=1
• Second block: (false OR (true AND false) OR NoImpact=0) → NoImpact=0
• Combined: Loss=1 AND NoImpact=0 → Matches your expected results.

Test Case 3: exec dbo.SearchErrorReports @ProfitSelected = 1, @LossSelected = 1, @NoImpactSelected = 1

• First block: (Profit=1 OR Loss=1 OR NoImpact=1) → Includes all records matching any selected checkbox
• Second block: (true OR ...) → Condition is always true, no exclusions applied
• Combined: Returns all records where Profit=1, Loss=1, or NoImpact=1 → Perfectly matches your expected output.

Optional: Handle "no checkboxes selected" #

If you want to return all records when no checkboxes are ticked (instead of returning nothing), add an extra condition to the first block:

(
    (@ProfitSelected = 1 AND Profit = 1)
    OR (@LossSelected = 1 AND Loss = 1)
    OR (@NoImpactSelected = 1 AND NoImpact = 1)
    OR (@ProfitSelected = 0 AND @LossSelected = 0 AND @NoImpactSelected = 0)
)

内容的提问来源于stack exchange，提问作者Noonmoon