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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s