How to achieve the equivalent of "short-circuit evaluation" in T-sql

Question

I have this select scenario:

I would like to return exact matches first, then only check for partial matches progressively, using a simple T-sql query.

select * from accounts where 
   mobile = @mobile or  
   mobile like @mobile +'%' or 
   mobile like '%'+@mobile or 
   mobile like '%'+@mobile +'%'

I understand that T-sql performs All-At-Once Operations.

How best can this be achieved?

Answer 1

You could evaluate in a CASE and provide a ranking value:

select
  mobile,
  case 
    when mobile = @mobile             then 1  
    when mobile like @mobile +'%'     then 2 
    when mobile like '%'+@mobile      then 3 
    when mobile like '%'+@mobile +'%' then 4
  end as [Rank]
from accounts where 
   mobile = @mobile or  
   mobile like @mobile +'%' or 
   mobile like '%'+@mobile or 
   mobile like '%'+@mobile +'%'
order by [Rank]

Answer 2

One way you could do it is to split your query into multiple queries. I'm not saying this gives the best performance, but:

select * from accounts
where mobile = @mobile

union

select * from accounts
where like @mobile +'%'
    and not exists (select 1 from accounts where mobile = @mobile)

union

select * from accounts
where mobile like '%'+@mobile
    and not exists (select 1 from accounts where like @mobile +'%')

union

select * from accounts
where mobile like '%'+@mobile +'%'
    and not exists (select 1 from accounts where like '%'+@mobile)

Something else that you can do, which is more "programatic" is to use @@ROWCOUNT, which should give better performance since it simulates short-circuiting.

select * from accounts
where mobile = @mobile

if @@rowcount = 0
    select * from accounts
    where like @mobile +'%'

if @@rowcount = 0
    select * from accounts
    where mobile like '%'+@mobile

if @@rowcount = 0
    select * from accounts
    where mobile like '%'+@mobile +'%'