Difference between NULLIF and ISNULL IN SQL SERVER 2005

NULLIF –

NULLIF returns NULL if both of the strings are equal else returns first string.

Syntax :
NULLIF(expression1, expression2)

e.g.
SELECT NULLIF(‘MakeFlag’, ‘MakeFlag’)AS ‘Null if Equal’
Output : NULL

SELECT NULLIF(‘FinishedGoodsFlag’, ‘MakeFlag’)AS ‘Null if Equal’ — FinishedGoodsFlag

NULLIF can be implemented using CASE statement. It will compare two parameters and return NULL if both expressions are same.
e.g.
SELECT ‘Null if Equal’ =
CASE
WHEN ‘MakeFlag’ = ‘FinishedGoodsFlag’ THEN NULL
ELSE ‘FinishedGoodsFlag’
END
Output : FinishedGoodsFlag

ISNULL() –

ISNULL is used to replace value of expression, if it comes to NULL.
Syntax :
SELECT ISNULL(NULL, expression)

e.g.
DECLARE @var varchar(50)
SELECT ISNULL(@var, ‘No value has been assigned to variable.’)
Output : No value has been assigned to variable.

ISNULL and NULLIF are exactly opposite to each other.
ISNULL returns NULL if comparison becomes successful.
On the other hand, NULLIF returns not null if comparison becomes successful.

Pawan Kumar / Gauri D
Pawankkmr@hotmail.com

Advertisements