if/else vs case

[#1]

Wondering if anyone has an opinion on the following. I have query where I need to eliminate the data in a field under a certain condition: if the quantity field of a record is 0 then I don't need to see the location field of that same record.

I've set up a case statement as such:

case when quantity = 0 then NULL when quantity > 0 then [Location] end) as [Location]

Then I saw if/else and set this up:

if quantity > 0 then [Location] else NULL endif as [Location]

They appear to pull and display the same data. Is there a benefit to either?

Thank you.

[VolkerBarth]

Just to add one big difference between IF and CASE expressions with ELSE clauses I came about based on Siger's FAQ on CASE exporessions with NULL values:

• With IF expressions, if the condition is UNKNOWN, the IF expression returns NULL - it does not return the value from the ELSE clause (which has been a surprise to me now and then...).

• With CASE expressions, if expression0 is NULL, then the value from the ELSE clause will be returned.

A simple repro:

begin
   declare n int = null;
   select
      if n = 1 then 'true' else 'false' end if, 
      case n when 1 then 'true' else 'false' end case,
      case when n = 1 then 'true' else 'false' end case;
end;

returns NULL, 'false', 'false'