TheSQLGuru (1/7/2009)

---

phystech (1/7/2009)

---

TheSQLGuru (1/7/2009)

...If you never passed in a NULL value then the CASE is surperfluous, right?..

No, zero length string plays the role of NULL. That is if the parameter is zero length then grab all the records (sort of).

Your "sort of" is a critical statement! 🙂

WHERE CASE

WHEN @FirstName IS NOT NULL THEN

CASE WHEN FirstName = @FirstName THEN 1

ELSE 0 END

ELSE 1 END = 1

The ELSE 1 will never be hit, thus the entire statement can be simplified to this (since you never have a NULL value passed in):

WHERE CASE WHEN FirstName = @FirstName THEN 1

ELSE 0 END = 1

and that, I believe, is no different logically than this:

WHERE FirstName = @FirstName

in which case you will obviously do an index seek if the statistics estimate 1-2% of the rows will be hit based on the incoming value of @FirstName.

And we are back to the parameter sniffing plan caching problem. :w00t: