Return string when no results

Question

What is the correct way to return a string when no results are returned. The following isn't working

SELECT 
TOP 1 CASE
WHEN
    CustomerName IS NULL
        THEN 
            'Unknown'
WHEN
    CustomerName = ''
        THEN
            'Unknown'
ELSE 
    CustomerName
END
AS
CustomerName FROM CUSTOMER WHERE CustomerCode = 222

I'm a bit confused by the DISTINCT TOP 1. I believe you need an order by so you know which TOP that is referring to. — Dave.Gugg, Jun 11 '14 at 15:22

FuzzyTree · Accepted Answer · 2014-06-11T15:30:54.950

1

It seems you want to return Unknown when there are no rows in your table that have CustomerName that's not NULL or not ''.

SELECT COALESCE((SELECT TOP 1 CustomerName FROM 
            CUSTOMER WHERE CustomerCode = 222
            AND CustomerName IS NOT NULL
            AND CustomerName <> ''),'Unknown') CustomerName

edited Jun 11 '14 at 15:30

answered Jun 11 '14 at 15:25

FuzzyTree

32,014
3
54
85

Anon · Answer 2 · 2014-06-11T15:32:51.637

1

If I understand the question, you want to return a value even when the WHERE clause doesn't match, and you want 'Unknown' to replace the empty string or a NULL value:

SELECT TOP 1 COALESCE(NULLIF(CustomerName,''),'Unknown')
FROM (
  SELECT CustomerName FROM CUSTOMER WHERE CustomerCode = 222
  UNION ALL
  SELECT NULL
) t
ORDER BY CustomerName DESC

edited Jun 11 '14 at 15:32

answered Jun 11 '14 at 15:26

Anon

10,660
1
29
31

Harshil · Answer 3 · 2014-06-11T15:28:48.770

Try this may be it's work..

    SELECT DISTINCT TOP 1 CASE WHEN ISNULL(CustomerName,'') <>'' THEN CustomerName ELSE 'Unknown'
 END as CustomerName FROM CUSTOMER WHERE CustomerCode = 222

or

SELECT TOP 1 CustomerName FROM 
(SELECT DISTINCT TOP 1 CASE WHEN ISNULL(CustomerName,'') <>'' THEN CustomerName ELSE 'Unknown'
 END as CustomerName ,1 No FROM CUSTOMER WHERE CustomerCode = 222
UNION 
SELECT 'Unknown',2)
AS T ORDER BY No

Return string when no results

3 Answers3