NULLIF (post, blank)

I revisited a neat little function in SQL Sever called NULLIF, and I thought it might be worth mentioning.  It came in very handy today when I used it to build a query on some summary data, it handled any divide-by-zero errors without much fuss.

NULLIF( param1, param2)
The function will return NULL if the comparsion of the two parameters is equal, otherwise it will just use the first parameter. 

select isnull( revenue / NULLIF(volume,0), 0 ) as [price] from sales_summary

A quick breakdown:

1. NULLIF(volume, 0)  -- if volume is 0 then this function will return NULL, otherwise it will return the volume
2. revenue / NULLIF(volume,0)  -- this is just my calculation (revenue/volume = price), if volume is zero the entire thing will return NULL (I like to think of it as exploding out as NULL)
3. ISNULL( revenue / NULLIF(volume,0), 0 ) -- The entire thing is wrapped by a ISNULL; if NULL is returned by NULLIF, it returns NULL for the entire calculation, which is handled by the ISNULL

An example:

begin tran t1

-- build some test summary data
declare @sales_summary table (
period int,
revenue money,
volume numeric(18,4)
)
;

insert into @sales_summary
select 1, 100.00, 50
union all
select 2, 0, 0
union all
select 3, 5000, 2500
union all
select 4, 30000, 20
;

select * from @sales_summary;

select
period,
revenue,
volume,
--(revenue / volume) as price --This will cause Divide-By-Zero for period 2
( revenue / NULLIF(volume,0) ) as [ex], --example showing the NULLIF returning NULL for period 2
isnull( revenue / NULLIF(volume,0), 0 ) as [price] -- Using the ISNULL to handle NULL and show 0
from @sales_summary
;

rollback tran t1