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


I want show result when


  @practiceid=1000


than only 1000 practices show and when 


  practiceid &lt;>1000


than show practiceid in (0,@practiceid)

but when i pass @practiceid=1000 than its also get 0 practiceid
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.