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

Notes :

To show the similarity between NULLIF and CASE, the following queries evaluate whether the values in the MakeFlag and FinishedGoodsFlag columns are the same. The first query uses NULLIF. The second query uses the CASE expression.