Using NULLIF to avoiding duplicating long T-SQL sub expressions for safe division, exclusive between

I seem to find myself generating a lot of SQL from other higher-level query structures. And it's bugged me for a while that if I have some long SQL expression, and then need to perform certain operations such as division, or exclusive BETWEEN, then it gets repeated.

So it seems like the ISNULL and NULLIF functions are awesome for solving this kind of problem.

The safe null-if-zero division problem

If we want to divide by some-long-expression and want to return NULL if it is zero, then we might find ourselves writing:

CASE WHEN some-long-expression = 0 THEN NULL ELSE numerator / some-long-expression END

But we can avoid including some-long-expression twice by using:

numerator / NULLIF(some-long-expression, 0)

The inclusive/exclusive BETWEEN problem

The BETWEEN clause gives us lower <= test-value <= upper, including both lower and upper in the test range. But sometimes we might need lower only, or upper only, or both, to be exclusive tests.

This can lead to duplicating long expressions by having to write:

lower < (some-long-expression) AND (some-long-expression) < upper

But we can use NULLIF to similarly avoid repeating the long expression being tested:

NULLIF( NULLIF( some-long-expression, lower), upper) BETWEEN lower AND upper

There's a few different combinations of scenarios we might need, as follows:

Include Lower	Include Upper	Include Null	SQL
Yes	Yes	Yes	ISNULL(expr, lower) BETWEEN lower AND upper
Yes	Yes	No	expr BETWEEN lower AND upper
Yes	No	Yes	NULLIF(ISNULL(expr, lower), upper) BETWEEN lower AND upper
Yes	No	No	NULLIF(expr, upper) BETWEEN lower AND upper
No	Yes	Yes	NULLIF(ISNULL(expr, upper), lower) BETWEEN lower AND upper
No	Yes	No	NULLIF(expr, lower) BETWEEN lower AND upper
No	No	Yes	NULLIF(NULLIF(ISNULL(expr, some-midpoint) lower), upper) BETWEEN lower AND upper
No	No	No	NULLIF(NULLIF(expr, lower), upper) BETWEEN lower AND upper