Monday, March 6, 2017

ISNULL Vs NULLIF

ISNULL : If first expression is NOT NULL then returns first expression value, If 1st Expression is NULL and 2nd expression is NOT NULL then it returns 2nd expression value, If both expressions are NULL then return NULL value.

DECLARE @A INT,@B INT,@C INT
SET @A=1
SET @B=NULL
SET @C=2

SELECT ISNULL(@A,@B), ISNULL(@B,@A), ISNULL(@B,@B), ISNULL(@A,@A)
GO

Result : 





NULLIF : Returns NULL value if the two specified expressions are equal or both expressions are NULL or first expression is NULL otherwise first expression data.

DECLARE @A INT,@B INT,@C INT
SET @A=1
SET @B=NULL
SET @C=2

SELECT NULLIF(@A,@B), NULLIF(@B,@A), NULLIF(@B,@B), NULLIF(@A,@A), NULLIF(@A,@C), NULLIF(@C,@A)
GO

Result :