If no rows exist from a list then display a message otherwise return the values-CodePudding

I have a tvp of account numbers that I am sending to a stored procedure from .NET code.

If the account number exists in the table then it will return data, however if it doesn't exist I need it to return a message of "Account Number doesn't exist" in the same select return statement as the valid accounts

This is my query

DECLARE @TVP_AccountNumbers dbo.TVP_AccountNumbers READONLY

BEGIN
    SELECT AccountId, AccountNumber, FirstName, LastName
    FROM Accounts
    WHERE AccountNumber IN (SELECT a.AccountNumber  
                            FROM TVP_AccountNumbers a)
END

This is my expected output

AccountId	AccountNumber	FirstName	LastName
1	123abc	John	Doe
null	Account number XXXX does not exist	NULL	NULL
3	456xyz	Jane	Doe

How can I get the expected output above?

Edit: Sorry Account ID will not exist for row 2. That was a copy paste issue

CodePudding user response：

If you left join then all null results will be ones that don't exist.

SELECT AccountId,
       COALESCE(a.AccountNumber,'Account number does not exist') as AccountNumber,
       FirstName,
       LastName
FROM Accounts
LEFT JOIN TVP_AccountNumbers a on Accounts.AccountNumber = a.AccountNumber

CodePudding user response：

With your update results of NULL in all the other columns, you could use a full outer join and a coalesce:

SELECT AccountId
    , coalesce(a.AccountNumber, 'Account Number XXXXX does not exist') AccountNumber
    , FirstName
    , LastName
FROM Accounts a
FULL OUTER JOIN TVP_AccountNumbers ta
    on a.AccountNumber = ta.AccountNumber