USE OF NULLIF IN SQL SERVER 2005
Syntax for NULLIF is NULLIF (expression ,expression )
NOTES about return type:
a) NULLIF returns the first expression if the two expressions are not equal.
b) If the expressions are equal, NULLIF returns a null value of the type of the first expression.
1. SELECT NULLIF(1,1) AS ‘Output’ — if both the expressions are equal then it will return NULL
2. SELECT NULLIF(9,1) AS ‘Output’ — if both the expressions are not equal then it will first expression as output
3. SELECT NULLIF(NULL,9) AS ‘Output’ — This will give error if the first expression is NULL
Msg 8133, Level 16, State 1, Line 1
None of the result expressions in a CASE specification can be NULL.
4. SELECT NULLIF(9,NULL) AS ‘Output’ — This will give output as 9
as both expressions are not equal and first expression is not null.