ALLINSIGHT

Home of the AlmostImplementedException

TSQL: NULLIF and ISNULL or How to prevent Divide-By-Zero errors

When you do calculations in your database, you sometimes have to deal with null and 0 (zero) values. This can lead to Divide-By-Zero errors or null-results. To prevent them we can use ISNULL and/or NULLIF.
Lets start with ISNULL. This simple fellow just needs two parameters. First the value to check and second the resulting value if the first is null.

This little snippet replaces salary with 1 if it is null.

NULLIF on the other hand does the opposite. If the first parameter has the same value as the second parameter, null is returned.

This statement returns null if the value of salary is 0.

But what can we do with this? ISNULL is good for getting results for your calculations, even if you have null values. With one null value in your calculation you would get null as result.
NULLIF can be used two get exactly that result. Division by zero is a big problem, but with NULLIF you can replace 0 with null and get null as result instead of an exception.

You can combine both to replace null and 0 with 1 (for multiplications or divisions a good way to ignore empty values).

This snippet replaces a 0 with null and afterwards replaces null with 1. Which means nothing else then 0 and null is replaces with 1.

Share :

,

Leave a Reply

Your email address will not be published. Required fields are marked *