SQL Conditional String compare in Where clause

Question

Using SQL Server, I have a stored procedure which I want to make a string search optional.

@search is a parameter. If @search has a value I want to search for the string, otherwise if it is empty it should bypass the search.

I just wanted to know your thoughts on the optimal way to write an optional string search in a WHERE.

Right now I have it as

AND (@search = '' OR [t4].number like '%'+@search+'%')

But what I haven't been able to find out is if the like evaluates even if @search = '' is true

If it does still compare both sides of the OR then I was thinking of using this

AND (CASE WHEN @search = '' THEN 1 
          ELSE (CASE WHEN [t4].number like '%'+@search+'%' THEN 1 
                     ELSE 0 END) END) = 1

Edit:I did a couple tests and it looks like the case does less reads.

Answer 1

I'm not sure how it would work with LIKE but my current favorite way to express that is to say:

select * from 
  mytable
 where myfield = COALESCE(@optionalValue, myfield)

And if @optionalValue is null then this evaluates to myfield = myfield otherwise myfield is checked against @optionalValue.

Edit: as it turns out, you can say:

select * from 
  mytable
 where myfield like ('%' + COALESCE(@optionalValue, myfield) + '%' )

And it seems to work just fine.

Answer 2

Expressions are generally evaluated from left to right, but there's no guarantee that will always be the case. You can help control the order with parenthesis, but the execution plan will depend on a lot of other factors.

However, comparing a varchar field against '%%' with LIKE will match everything anyway.