Dynamic WHERE statement - SQL server 2005

Question

I need to create a SQL statement that uses the LIKE keyword to filter the result set. Sometimes this value is NULL.

What is the best way to implement in T-SQL? I am using SQL server 2005

Answer 1

Use coalesce!

COALESCE(filter, '%')

Edit: This is probably the wrong way to do it but I guess it's an option.

Answer 2

Depends what you mean by "not provided". If that means NULL, you can always try

(
    Table.Column LIKE @myParameter
OR
    @myParameter IS NULL
)

I've also found this to sometimes work faster.

ISNULL(Table.Column, @myParameter) = @myParameter

Alternatively, if you dynamically building the TSql from code, just ommit that part of the WHERE clause if you don't have the parameter or populate @myParameter with '%'.

Answer 3

 SELECT 
 lalala
 WHERE 1=1
 and (
  value like '%'+@search+'%'
  or @search = ''
 )

Although I'm not sure how it will perform...

Answer 4

Personally, this is the sort of situation where I'd build the SQL statement in the VB/C# code, and just not concatenate the LIKE clause on the end if I didn't have it.

The slightly fancier version of what I just said is to have two versions of a prepared statement, one with and one without the LIKE clause, and call the appropriate one as you need to.