Using SQL Where Clauses for Searching

Using SQL Where Clauses for Searching

"...
if len(@firstname) > 0
set @where = @where + " AND firstname LIKE '" + @firstname+ "'"

if len(@lastname) > 0
set @where = @where + " AND lastname LIKE '" + @lastname+ "'"

--uggggh

This is time consuming and doesn't allow SQL server to optimize your queries. And it just isn't fun :).

Now check this out. Using our trusty COALESCE, we can get it all in one SELECT:
SELECT * FROM Customers
WHERE
ISNULL(firstname,'') = COALESCE(@firstname,firstname, '')
AND isnull(lastname,'') = COALESCE(@lastname,lastname, '')
AND isnull(phone,'') = COALESCE(@phone,phone, '')
AND isnull(email,'') like COALESCE(@email,email, '')
AND isnull(address,'') like COALESCE(@address,address, '')"

..."

I've seen this technique posted a couple times, but this post does a great job of explaining the idea and concepts behind it. Thereby warrants being added to my remote memory store for later reterival...