Friday, May 06, 2005

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...

2 comments:

Anonymous said...

This is especially handy with code generation. We generated a SearchBy stored procedure that took optional parameters for all fields, and the generated class had a SearchBy static method that returned a custom collection, datatable, or dataset filled from the stored procedure output.

I think the COALESCE method is more likely to result in table scans, though.

Greg said...

Nice.

If/when I use COALESCE in a real world project I'll make sure to watch the query plans... Thanks for the tip.