Where clause no give expected result

Question

where clause not show expected result.

declare @PracticeId bigint =1000
select * from insurance i
WHERE ISNULL(I.DELETED,0)=0 
    and (
          (  @PracticeId =1000 and(I.PracticeId=@PracticeId and I.PracticeId<>0) ) 
        OR( (I.PracticeId=0 OR I.PracticeId=@PracticeId) and I.PracticeId<>1000 )
    ) 

I want show result when

@practiceid=1000

than only 1000 practices show and when

practiceid <>1000

than show practiceid in (0,@practiceid)

but when i pass @practiceid=1000 than its also get 0 practiceid

Answer 1

Your logic problem occurs in the way you are ORing i.PracticeId=0 into the criteria. That row is ORed so that it's always true. Effectively, you're using OR when you want it to act like ELSE (that's not how OR works).

I believe you want to add in this AND @PracticeId<>1000 to get the desired results:

declare @PracticeId bigint =1000
select * from insurance i
WHERE ISNULL(I.DELETED,0)=0 
    and (
          (  @PracticeId =1000 and(I.PracticeId=@PracticeId and I.PracticeId<>0) ) 
        OR( ((i.PracticeId=0 AND @PracticeId<>1000) OR I.PracticeId=@PracticeId) and I.PracticeId<>1000 )
    )  

Note: this query will probably perform terribly and be very slow due to the way the criteria are ORed together. Solving this with dynamic SQL or an IF/ELSE block would likely simplify your logic and perform much better.