Translating Oracle's NVL2 function to Microsoft SQL Server
2007-03Mar-29
On Technet Microsoft states thatthe proper translation for Oracle's NVL2 function:
NVL2 (Salary, Salary*2, 0)
CASE SALARY
WHEN null THEN 0
ELSE SALARY*2
END
They got the Oracle part right, but miserable failed on the syntax of their own server. The correct way
to use CASE with NULL values is
CASE
WHEN SALARY IS NULL THEN 0
ELSE SALARY*2
END
In Microsoft SQL Server you use IS NULL and IS NOT NULL to check for NULL. The original expression would
always return the value from the ELSE part since the condition SALARY=null never becomes true.