Imagine you have several pieces of criteria that the user may use to query the database. They probably have a nice dashboard where they can choose from drop-down lists and maybe even search using text!
I’ve seen many creative ways to manage SQL queries for reports and/or searches. Some methods of creating the SQL are better than others. This article describes one anti-pattern and one best practice that can be used in place of the anti-pattern.
Imagine that the input parameters for this query are:
The anti-pattern code looks something like this:
var x = 'select ColOne, ColTwo from MyTable where';
x+='theColumnIWantToCheck like p_c1'
x+='and thirdColumn = p_c2 and fourthColumn = p_c2'
exec x --Execute the string using your favorite database...
I’ve seen this pattern more times than I care to mention. It’s also a maintenance nightmare. Over time new criteria are added and the many branches in the if/then logic become convoluted at best.
I once saw a nasty version of this anti-pattern that spanned more than 50 pages when printed! Ouch! …and you guessed it, my job was to add “just one more” criteria to it! The core problem here is that we’re not allowing the database to do what it is good at. Databases LOVE to search and sort data!
A Best Practice:
A better way to handle the same problem utilizes short circuit evaluation. The basic pattern is this:
(p_my_parm==null or (condition_to_test_here))
This allows you to pass NULL in when you don’t want to search on a specific criteria. If you *do* want to search on it, then pass in a value. The second half of the condition will only run when p_my_parm is NOT null. I typically encourage developers to leave the second set of parenthesis in even when they’re not necessary. This tends to make the SQL more readable and understandable for less experienced software engineers.
Select ColOne, ColTwo, ColThree from myTable where
(p_c1==null or (theColumnIWantToCheck like p_c1))
and (p_c2==null or (thirdColumn = p_c2 and fourthColumn = p_c2))
The end result is a single SQL that is readable. It’s not surrounded by if statements, strings, and other syntax that make it hard to comprehend. Instead it exists as a single, elegant SQL statement.
In almost every case this approach comes out miles ahead of our anti-pattern in terms of both performance and maintainability. Your software engineers will be able to more quickly read and understand the SQL and your database engine can create an awesome query plan to execute the search.