user2463808 user2463808 - 1 month ago 8
SQL Question

SQL return values if not null, return zero when null

If this simple query return no results (NULL), it returns zero in one row, which is correct. If there are some results, query returns error 512.

How can I modify it to return the values if found and "0" if not?

select ISNULL( (select Name from NAMES
left join ADDRESS on NAMES.Name = ADDRESS.Person
where
NAMES.Name = myinput
) , 0) as Name

Answer Source

Wrap the main query into CTE and now

with q as(
    select Name 
    from NAMES
    left join ADDRESS on NAMES.Name = ADDRESS.Person
    where
    NAMES.Name = myinput
)
select Name 
from q
union all
select '0' 
where not exists(select 1 from q);