NULLIF (Transact-SQL)

Yesterday I found one more function of sql server. NULLIF which returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression. NULLIF function is available from Sql Server 2005.

Syntax :

NULLIF ( expression , expression )

Example :

The following example creates a budgets table to show a department (dept) its current budget (current_year) and its previous budget (previous_year). For the current year, NULL is used for departments with budgets that have not changed from the previous year, and 0 is used for budgets that have not yet been determined. To find out the average of only those departments that receive a budget and to include the budget value from the previous year (use the previous_year value, where the current_year is NULL), combine the NULLIF and COALESCE functions. 

ISNULLIF Example