2007-03Mar-29

Translating Oracle's NVL2 function to Microsoft SQL Server

On Technet Microsoft states that the proper translation for Oracle's NVL2 function:

NVL2 (Salary, Salary*2, 0)

would be

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.