SQL case 0 then NULL

May be you need ELSE:

SELECT 
  ID, Firstname, Lastname CASE Number WHEN 0 THEN NULL ELSE Number END
FROM tPerson

SELECT ID, Firstname, Lastname,
 CASE WHEN Number!=0 THEN Number END 
FROM tPerson

As others have mentioned you forgot to tell your CASE statement to return the number in case the number is not null.

However in SQL Server you can use NULLIF, which I consider more readable:

select 
  id, 
  firstname, 
  lastname, 
  nullif(number, 0) as number
from tperson;

If you want to stick to standard SQL then stay with CASE:

case when number = 0 then null else number end as number

Related