Wow, NULL IS NOT NULL is just killing us :-(.

I have seen code like this before, it is called "job insurance" style

and people thought terry childs was paranoid

This looks like it was dynamically generated. The "IN ( NULL )" sections in particular look to be dynamically generated - I would guess that the parent code has a string of IDs and it is supposed to generate something like "r.DoctorResourceID IN ( 1243, 1244, 236 )" but somehow the strings are empty/NULL.

Oh my, is someone posting that at thedailywtf.com or what? The null is not null part is a riot!

ok, this looks kinda scary but its obvious what it is doing. It probably came from a stored procedure with a lot of optional parameters.

For example, let´s assume we are filtering on a resource, then we would have.

( ( 0xDEADBEEF IS NOT NULL

AND r.DoctorResourceID IN ( 0xDEADBEEF ) )

OR ( 0xDEADBEEF IS NULL ) ),

wich would retrieve all rows from the recall table that referenced the doctor resource association table wich had a primary key of 3735928559.

I´m not saying that it is pretty or even that I would use such an abomination ... well ... maybe when I´m given a crappy deadline.

I just think that he could at least leave the parameters unchanged instead of replacing them with NULLs all over the place. This is typical enterprise suckiness at its best ... At least it´s not 200 lines long and references half the tables in the application´s model.

as I said before, this probably came from a sproc with optional parameters.

It turns out, you actually need the NULL IS NOT NULL part. It works as a safeguard so the "AND r.DoctorResourceID IN ( NULL )" does not get evaluated thanks to boolean short-circuiting.

Given a non-null value, it would evaluate both parts and return true.

Given a null value, it evaluates the parenthesized AND expression as false, but the expression after the OR would evaluate as true.

The poor fella seems to be using string interpolation. I´m not a developer anymore, but I always remember seeing the puzzled eyes of junior developers staring at a query like this. They still had souls back then.