Blog Archives
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
