So I have a simple query as follows;
SELECT Name FROM Contact WHERE Age > @age
My question is, I want this query to always return a value. If there are no results the SQL query is suppose to return ' ' (blank space).
I have added a UNION SELECT at the end;
SELECT Name FROM Contact WHERE Age > @age UNION SELECT ' '
However this also adds the blank record when there is a result.
IF NOT EXISTS(SELECT Name FROM Contact WHERE Age > @age)
BEGIN
SELECT '' AS age
END
ELSE
BEGIN
SELECT Name FROM Contact WHERE Age > @age
END
This will check if a result set will return and if nothing will, select a blank space and name the field age.
I would do this all in a single query:
select name
from contacts
where age > @age
union all
select v.name
from (values ('')) v.name
where not exists (select 1 from contacts where age > @age);
Or using a CTE:
with t as (
select name
from contacts
where age > @age
)
select t.*
from t
union all
select v.name
from (values ('')) v(name)
where not exists (select 1 from t);
Or, the fun way using a left join:
select coalesce(c.name, v.name) as name
from (values ('')) v(name) left join
contacts c
on c.age > @age;
