How Coalesce works in sql server?

Question

Create table test(Names varchar(100) primary key )

insert into test values('Hugeman')
insert into test values('Jack')
insert into test values('William')
insert into test values('Kevin')
insert into test values('Peter')

Query 1:

declare @sql varchar(100)
select   @sql = coalesce(@sql+'+','')+Names from test order by names-- where object_id =object_id('temp')  
print @sql

This will result as Hugeman+Jack+Kevin+Peter+William

Query 2

declare @sql varchar(100)
select   @sql = coalesce(Names+'+','')  from test order by names-- where object_id =object_id('temp')  
print @sql

This will results William+

As per the documentation of coalesce, will return the first not null value. So it has to result Hugeman+. But it returns the entire rows.

Why query2 haven't done the same ?

Answer 1

This is not stricly connected to COALESCE.

Try these SELECTs:

DECLARE @sql1 AS VARCHAR(1000)
SELECT @sql1 = ISNULL(@sql1, '') + Names FROM test ORDER BY Names
PRINT @sql1

DECLARE @sql2 AS VARCHAR(1000)
SELECT @sql2 = Names FROM test ORDER BY Names
PRINT @sql2

So what happened? For EACH record selected:

• in query 1 you keep adding values to @sql
• in query 2 you reset @sql as the last name extracted

I don't understand exactly what you want to obtain from your SELECT, but a better example of COALESCE could be:

CREATE TABLE TEST2(Name VARCHAR(100) PRIMARY KEY, Telephone VARCHAR(10), Mobile VARCHAR(10))

INSERT INTO TEST2 VALUES('Hugeman', 1, 2)
INSERT INTO TEST2 VALUES('Jack', NULL, 3)
INSERT INTO TEST2 VALUES('William', 4, NULL)
INSERT INTO TEST2 VALUES('Kevin', 5, 6)
INSERT INTO TEST2 VALUES('Peter', NULL, NULL)

SELECT Name,
    COALESCE(Telephone, Mobile) AS Tel
FROM TEST2