Best query approach when using NULLABLE columns in parameterized SELECT
Posted on 2007-11-22
I'm just past the newbie stage of SQL Server with respect to learning more than how to build an ad-hoc query using user input. Several variables play into my question.
1) I recognize the need to parameterize my queries to protect against SQL injection, among other things.
2) I give my customer the ability to provide the values for 1 or more columns - some of which are NULLABLE.
3) I have been using the FilterExpression with my adhoc queries but have been dissatisfied with the inability to discern the actual number of filtered items. (Plus - is it performant???)
4) I recognize that "unnamed" ad-hoc queries are very likely the least performant of all queries, and I have been reading up on stored procedures, since they "sound" like a good thing.
5) I'm trying to determine the overall best approach to having a "named" (aka performant) query, parameterized, which accounts for the possibility that one of my optional parameters may be on a NULLABLE column - in which case if it happens to be NULL when I specify NULL for the parameter value to indicate that I'm not querying on it this time - that the row will still be selected.
6) I started converting my ad-hoc query into a parameterized stored procedure. All went well enough until I noticed I was not getting all of the rows due to this NULLABLE column that contained a NULL value.
7) ISNULL does not solve the problem.
8) In searching the internet about this problem, I've read article after article about the pros and cons of Dynamic SQL vs stored procedures and coalese vs ISNULL. It appears to be a hotly debated topic to put it mildly. To be honest, the Monty Python "It's Just a Flesh Wound scene" comes to mind when I read the articles and the rebuttals.
(And now to my question - at the risk of getting my own arms or legs cut off... ) :
I really need expert advice on the best approach to how to make a performant, parameterized query that accepts optional parameters on columns that may be NULLABLE. Many many thanks in advance.