Where Clause based on condition

Question

I currently have a select statement that changes based on a condition:

if @LoadUsage = '|CreateBatch|'
                        begin
                            -- Do lookup on ClaimNumber and InterchangeName provided
                            -- Use @version scalar to filter latest InputAdjustmentVersion
                            select @ClaimSubmissionNumber = CS.ClaimSubmissionNumber 
                            from ClaimSubmissions CS inner join Claims C on C.ClaimID = CS.ClaimID
                                               inner join InterchangeInfo I on CS.InterchangeId = I.InterchangeId
                            where I.InterchangeName = @InterchangeName
                                and C.ClaimNumber = @ClaimNumber
                                and CS.SubmissionStatus in (select SubmissionStatus from @AllowedSubmissionStatus)
                                and Cs.InputAdjustmentVersion = @Version
                        end
                    else
                        begin
                            -- Do lookup on ClaimNumber and InterchangeName provided
                            select @ClaimSubmissionNumber = CS.ClaimSubmissionNumber 
                            from ClaimSubmissions CS inner join Claims C on C.ClaimID = CS.ClaimID
                                inner join InterchangeInfo I on CS.InterchangeId = I.InterchangeId
                            where I.InterchangeName = @InterchangeName
                                and C.ClaimNumber = @ClaimNumber
                                and CS.SubmissionStatus in (select SubmissionStatus from @AllowedSubmissionStatus)
                        end

If the @LoadUsage scalar is '|CREATEBATCH|', I add another check ( and Cs.InputAdjustmentVersion = @Version) to the where clause. Is there a way to do this with a case statement in the where clause instead of having two different select statements?

[This](https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) answer demonstrates using a `case` _expression_ in an `on` clause. It can be used the same way in a `where` clause. You should read [Dynamic Search Conditions in T‑SQL](http://www.sommarskog.se/dyn-search.html) and [Revisiting catch-all queries](https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/) for information about the performance implications. — HABO, Nov 04 '19 at 16:12

score 1 · Accepted Answer · answered Nov 04 '19 at 14:46

Simply use OR:

select @ClaimSubmissionNumber = CS.ClaimSubmissionNumber 
from ClaimSubmissions CS inner join Claims C on C.ClaimID = CS.ClaimID
                   inner join InterchangeInfo I on CS.InterchangeId = I.InterchangeId
where I.InterchangeName = @InterchangeName
    and C.ClaimNumber = @ClaimNumber
    and CS.SubmissionStatus in (select SubmissionStatus from @AllowedSubmissionStatus)
    and (@LoadUsage <> '|CreateBatch|' or Cs.InputAdjustmentVersion = @Version)

score 1 · Answer 2 · answered Nov 04 '19 at 14:47

yes, you should be able to add the @LoadUsage = '|CreateBatch|' to the WHERE, and have the appropriate logic depending on whether true or false . e.g.

WHERE @LoadUsage = '|CreateBatch|' AND (..logic 1....) 
         OR  @LoadUsage <> '|CreateBatch|' AND (...logic 2...)

Where Clause based on condition

2 Answers2