This entry is a quick MS SQL gotcha. One of my employer’s clients has written a set of rules as fragments of SQL code in their back office database. I was called in the other day because the client insisted that their SQL fragment was correct and that we were doing something wrong. Guess what! Their SQL was flawed. However, their flaw was quite subtle and it was tricky to track down. The client provided a SQL fragment which looked like this:
CASE WHEN ( SELECT ISNULL(someColumn,0) FROM someTable WHERE someOtherColumn = 'someOtherValue' ) < 1 THEN 35 ELSE 50 END
The rule was evaluated by dropping the SQL fragment into another query. In general it looked like this:
<cfquery name="runRule" datasource="#mydatasource#"> SELECT #SqlRule# as value </cfquery>
The SqlRule variable was the text of the case statement above. When the subquery in the case statement returned NULL we would return 35 in the value column. When the subquery returned a value which was not null would return 50. What do you think happened if the subselect returned no rows? If you guessed that we would receive 50 in the value column then you’re much smarter than me. I thought the answer would be 35. From the perspective of SQL, what does a lack of data equate to? NULL. If there are no rows and you’re comparing no rows to 1 then would you not be comparing NULL to 1? And is NULL less than 1? It turns out that NULL is not less than one. It also turns out that NULL is not greater than one. Try running these two queries:
SELECT 'ah ha!' WHERE NULL < 1 SELECT 'ah ha!' WHERE NULL > 1
They both return no rows. So, if NULL < 1 evaluates to false then SQL will return the else portion of the case statement, or 50!