SQL Server Tip: NULLIF

To my shame, I’d never come across this in-built SQL Server function unless today. The NULLIF function allows you to specify a NULL if the result was going to be something else:

SELECT NULLIF(3+1,4)

will return NULL. Full details here on the MSDN site.

This has fixed my own immediate problem: that of diving by zero stopping play. Because I’m calculating percentages of run rates – if the initial value is zero, trying to work out a percentage causes the calculation stoppping:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Helpfully though, by surrounding the value with a NULLIF to return a NULL, the enitre division calculation will be evaluated as a NULL: an apporiate conclusion for this sort of outcome.