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:
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.
Nice.
If/when I use COALESCE in a real world project I'll make sure to watch the query plans... Thanks for the tip.
Post a Comment