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

OUTPUT
________________

NULL

2. SELECT NULLIF(9,1) AS ‘Output’ — if both the expressions are not equal then it will first expression as output

OUTPUT
______________

9

3. SELECT NULLIF(NULL,9) AS ‘Output’ — This will give error if the first expression is NULL

OUTPUT
______________

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.

OUTPUT
______________

9

Happy Programming.

Pawan Kumar
Pawankkmr@hotmail.com

Advertisements