Before deciding where to use the function NULLIF() in SQL Server , Let’s first find out what is purpose of NULLIF().
NULLIF() is used to return NULL on a matching expression.
Let’s take an example-
Here if the value of @VAR is 0 then it will return NULL.
Now, Think where can we use this?
For this we will take another example-
DECLARE @VAR INT SET @VAR=0 SELECT 100/@VAR
Divide by zero error encountered.
Some time we may need to show the result as NULL if the variable @VAR has a value of 0.
Now, we can re-write the SELECT statement as -