Where clause no give expected result
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&lt;&gt;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&lt;&gt;0) ) 
        OR( ((i.PracticeId=0 AND @PracticeId&lt;&gt;1000) OR I.PracticeId=@PracticeId) and I.PracticeId&lt;&gt;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.