Today I was reading in Joseph Sack’s book SQL Server 2008 Transact-SQL Recipes and discovered a jewel of a tip. The jewel is located on page 275.
“So what is the jewel?”, you are asking. Well I am glad you asked. Let’s say you have 30 different criteria for which to search for a particular row or rows in your favorite table. It could have been two criteria, but for effect let’s say it is thirty. Now your favorite user has told you that he may search this table today with 2 criteria and tomorrow with 10 and on another day with 5 and oh by the way, it will always be in different combinations of search criteria.
I don’t remember the formula for computing the number of combinations but it is a very large and imposing formula using factorials. Not only is the formula imposing, but the result of the formula is even more so. The number of combinations is quite sizable when there are 30 possible criteria . The number is quite imposing if you were thinking of suggesting a solution that involved multiple stored procedures (1 for each combination) or multiple IF statements if you were about to suggest utilizing those in some rather lengthy fashion.
So now you are asking, what other possibility is there? Well the solution is the SQL function ISNULL. Let me explain how ISNULL works. ISNULL takes two parameters. The first parameter will be checked to determine if it is null and if it is null, then the second parameter will be returned. If the first parameter is not null then the first parameter will be returned.
If @OrderNumber is not null then the value of the parameter (or local variable) @OrderNumber will be returned. On the other hand if @OrderNumber is null then the column value OrderNumber will be returned. However looking at this out of context might very well leave you with a blank stare on your face so let me see if I can contextualize it for you.
So we have the following query:
OrderID = ISNULL(@OrderID, OrderID) AND
CustomerID = ISNULL(@CustomerID, CustomerID)
The WHERE clause will compare the OrderID column value to the @OrderID parameter (or local variable) if and only if the @OrderID value is not null. If @OrderID is null then the value of the OrderID column will be returned and it will always be equal to itself.
This allows you to code as many criteria as you like and if the criteria is NULL then it is ignored. This technique yields a huge benefit when you need to use multiple optional selection criteria.
Again this was an idea I read in Joseph Sack’s excellent book SQL Server 2008 Transact-SQL Recipes. You may follow Joe at http://www.joesack.com