Where to use NULLIF() in SQL Server ?


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-
Example:

 
SELECT NULLIF(@VAR,0)

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-
Example:

DECLARE @VAR INT
SET @VAR=0
SELECT 100/@VAR

Result:
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 –
Example:

SELECT 100/NULLIF(@VAR,0)

Result :
NULL

Advertisements