15

Is it possible to use CASE to return a certain string if there are no results from my SELECT statement?

Example: 

DECLARE @accountnumber AS VARCHAR(10)

SET @accountnumber = 'account number to search'

SELECT 
    CASE
    WHEN account IS NOT NULL
    THEN 'We Have Records of this Customer'
    WHEN account IS NULL
    THEN 'We Do Not Have Records For This Customer'
    END AS 'result'
FROM call_records
WHERE account = @accountnumber
GROUP BY account

The above does not work since if the account number I am searching for isn't present in my logs table then there would be no results and the message 'We Do Not Have Records For This Customer' would never materialize.
I can do what I am trying to achieve using pure T-SQL with a PRINT command, but I am working with a 3rd party app and the results must be in table form (so SELECT statements only).

Lloyd Banks
  • 35,740
  • 58
  • 156
  • 248
  • Might there be more than one matching record for an `account` in `call_records`? If so do you just want a single row result? – Martin Smith Feb 18 '13 at 16:54
  • 1
    @MartinSmith: Note the final line of the supplied query (I missed it when I first read the question, too). –  Feb 18 '13 at 16:56
  • @MarkBannister - Good point. Even if there are then it will return a single row. – Martin Smith Feb 18 '13 at 16:56

2 Answers2

28

You can use EXISTS:

SELECT 
    CASE
    WHEN EXISTS(
        SELECT 1 FROM call_records
        WHERE account = @accountnumber
    ) 
    THEN  'We Have Records of this Customer'
    ELSE  'We Do Not Have Records For This Customer'
    END AS 'result';
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
6

Try:

DECLARE @accountnumber AS VARCHAR(10)

SET @accountnumber = 'account number to search'

SELECT 
    CASE
    WHEN account IS NOT NULL
    THEN 'We Have Records of this Customer'
    WHEN account IS NULL
    THEN 'We Do Not Have Records For This Customer'
    END AS 'result'
FROM (select 1 dummy) dummy
left join call_records ON account = @accountnumber
GROUP BY account