NULLIF function in sql server

The NULLIF () function returns NULL if two expressions are equal, otherwise, it returns the first expression.

Syntax

NULLIF ("expression 1", "expressions 2")

We will get the same result using the following CASE statement

CASE   WHEN "expression 1 = expression 2 " THEN NULL   ELSE   "expression 1"   END

Let’s see the example.

 

If both the expression value is the same in this case it will return Null.

If both the expression is not the same in this case it will return the first expression value.

 

Use of NULLIF function

This function we are using generally in the mathematical calculation like dividing a number or find the percentage.

declare @a int=25,                  @b int=5; select @a/@b

It will return to 5.

Suppose we are getting the value of @b is zero then that will happen. We will get the error.

Suppose we are using this script in SP then SP will fail. To avoid such type of error we need to handle it in the script. If we get the value of @b is zero then we need to set the result as zero.

With the help of NULLIF function we will handle such type of issue.

Let’s see the below example.

As we discuss above if expression 1 and expression both value is same in this case it will return the null value and if we divide any number with null in this case we will get the result as null. Finally with the help of the ISNULL function we will convert null to zero value.

Read More about ISNULL function: Different ways to replace A NULL value in SQL server

https://bageshkumarbagi-msbi.blogspot.com/2015/12/different-ways-to-replace-null-in-sql.html