CODEWITHSUNDEEP
sqlsql-server
user2232273
user2232273

Reputation: 4964

SQL Server - COALESCE WHEN NOTHING RETURNS , GET DEFAULT VALUE

I'm trying to use Coalesce function in SQL Server to concatente multiple names. But when the conditon in the query returns no rows or nothing, I need to return a default value. I tried some condition using case statement but I can't figure it out what I missed.

declare @Names varchar(max) = '',
        @Key varchar(max) = 'ABC' 

select @Names = COALESCE(@Names, '') + isnull(T0.A, @Key) + ', ' 
from TData P
left join TNames T0 on T0.C + '\' + T0.D = P.@Key
where OBID=581464 
and ID < 1432081

select @Names

Upvotes: 1

Views: 308

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

You can do it with 2 minor changes to your current code, but I suspect this is an XYProblem, and you might benefit more from editing your question to include sample data and desired results (so perhaps we can suggest a better solution).

Anyway, what I had in mind is this:

declare @Names varchar(max), -- remove the = '', so that @Names starts as null
        @Key varchar(max) = 'ABC' 

select @Names = COALESCE(@Names, '') + isnull(T0.A, @Key) + ', ' 
from TData P
left join TNames T0 on T0.C + '\' + T0.D = P.@Key -- ' (This is just to fix the coding colors)
where OBID=581464 
and ID < 1432081

select COALESCE(@Names, 'default value') -- since @Names started as null, and the query did not return any results, it's still null...

Upvotes: 1

Related Questions