tigriswoods
asked on
Best query approach when using NULLABLE columns in parameterized SELECT
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.
Tigriswoods
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.
Tigriswoods
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hard to believe it's that simple but my initial testing is looking positive. I'll test it more thoroughly and get back to you. THANKS !
<<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. >>
In short, there are satisfactory solution with evaluating NULLS. The best way to deal with NULLS is to not use them at all...For optional attributes, create an additional table and establish a 1:0 relationship with the mandatory table based on the base table.
Ex: suppose you have an *employee* (emp#, fname, lname, fixed_phone) table that may or may not have a cell phone number. The correct solution to the problem is to create an *employee_cellphone* table with(emp#, cellphone). That way, once you want you just do an inner join it will be much easier to handle....
Hope this helps...
Hope this helps...
In short, there are satisfactory solution with evaluating NULLS. The best way to deal with NULLS is to not use them at all...For optional attributes, create an additional table and establish a 1:0 relationship with the mandatory table based on the base table.
Ex: suppose you have an *employee* (emp#, fname, lname, fixed_phone) table that may or may not have a cell phone number. The correct solution to the problem is to create an *employee_cellphone* table with(emp#, cellphone). That way, once you want you just do an inner join it will be much easier to handle....
Hope this helps...
Hope this helps...
ASKER
Yo habla un poquito SQL solamente (I speak only a litle SQL...)
I am presenting data from many tables in a gridview based on the search criteria entered by the customer. One of the NULLABLE fields is the result of a LEFT OUTER JOIN of one table (Results) with another table (TestOrders). The TestOrders exist, but the Results for the Order may not yet exist. I need to ensure I do not exclude any TestOrders which have no Results -- unless I'm using the Results_received_date as part of the criteria. In that case, only TestOrders which have Results will be displayed in the gridview. The prioblem I was having (and which appears to have been solved by the suggestion from imitchie) was that this statement in my sproc would exclude TestOrders for which Results did not yet exist whenever I queried on other columns:
AND (@Results_received_date >= ISNULL(@Result_received_da
I still have lots to learn -- I was also wondering about using a sproc vs creating a Dynamic SQL and executing it via EXEC. Now that it appears that the sproc will work after all, I'm hoping I'm heading in a better, more performant direction than creating on-the-fly queries in my code. :)
I am presenting data from many tables in a gridview based on the search criteria entered by the customer. One of the NULLABLE fields is the result of a LEFT OUTER JOIN of one table (Results) with another table (TestOrders). The TestOrders exist, but the Results for the Order may not yet exist. I need to ensure I do not exclude any TestOrders which have no Results -- unless I'm using the Results_received_date as part of the criteria. In that case, only TestOrders which have Results will be displayed in the gridview. The prioblem I was having (and which appears to have been solved by the suggestion from imitchie) was that this statement in my sproc would exclude TestOrders for which Results did not yet exist whenever I queried on other columns:
AND (@Results_received_date >= ISNULL(@Result_received_da
I still have lots to learn -- I was also wondering about using a sproc vs creating a Dynamic SQL and executing it via EXEC. Now that it appears that the sproc will work after all, I'm hoping I'm heading in a better, more performant direction than creating on-the-fly queries in my code. :)
<<I was also wondering about using a sproc vs creating a Dynamic SQL>>
nothing prevents you from using dynamic SQL inside a stored proc...
nothing prevents you from using dynamic SQL inside a stored proc...
ASKER
At this stage of the game, the suggestion by imitchie has resolved the problem I was having. Thank you!! I have tested this thoroughly and it is working just fine. Whether or not this is the most performant query, I cannot say, but at least I now have a parameterized query using NULLABLE columns.
ASKER
Thanks again!
where (@filterableColumnFilter is null) or (filterableColumn = @filterableColumnFilter)
and (@filterableColumnFilter2 is null) or (filterableColumn = @filterableColumnFilter2)