We have been doing that at my company until recently. A problem we discovered is that using COALESCE or ISNULL will cause a full-table scan.

The COALESCE trick works wonders until a column supports NULLs and you didn't pass in a criteria for that column. i.e. the following clause:

WHERE FirstName = COALESCE(@firstName,FirstName)

would become

WHERE FirstName = FirstName

and wherever FirstName is NULL would be filtered out of the result set. Which may or may not be the behavior you want. 9 times out of 10 it isn't the behavior I want. You could try wrapping the left side in a coalesce also and adding another option to the right side...

WHERE COALESCE(FirstName, '') = COALESCE(@firstName, FirstName, '')

Just goes to show you that every solution just creates more problems! :)

I just ran a test of my own, and it *does* appear that there is a difference between a WHERE clause using the COALESCE function vs. one that does a straight equals comparison.

For example, the execution plan for the following code shows that it is doing an index seek:

declare @userid int
SET @userid = 100848

select * from media where userid = @userid

However, the plan for the following code block show that it is doing a clustered index *scan* on the primary key field (mediaid):

declare @userid int
SET @userid = 100848

select * from media where userid = coalesce(@userid,userid)

So I expect that it will be somewhat slower than a straight equals comparison on a very large table. So I guess it won't be the ideal solution in all scenarios, but I think it's still quite handy for things like dynamic reporting on relatively small tables.

Yeah, an index scan isn't as bad as a full table scan.

Yeah, sorry about that, I spoke incorrectly. It was an index scan that we were having problems with as well, and it was enough of a performance hit for our purposes that we had to do away with it.

We did have a problem with full table scans, but that's because I'm an idiot with indexes.

Thanks for the clarification!

This article has a very exhaustive rundown on all the options and performance implications on just about every possible approach to dynamic WHERE and ORDER BY queries:

http://www.sommarskog.se/dyn-search.html

The problem is that SQL sucks and the people that are set to handle SQL completely refuse to add anything useful to the standard. (Celko?)

What we need is for SQL to add dynamic where, dynamic order by in an easy and optmized way, we also need arrays, vectors whatever you want to call it.

It's time to mondernize SQL !