Why doesn't this work... 1

[simian101]

I have a query that I wrote that does not work, and a couple of versions that do. I don't understand why the first one does not work.

Basically I want to take a varchar field and concat it into a single value like below.

List....

5524289008
5524289011
5524255010
5524255097
5524251254
5529981254
7045421254
5524281594
5524252412
7045427224
5524282542
5529985248
5524250950
5524254500
5524254575
5524252005

Result.
5524289008 5524289011 5524255010 5524255097 5524251254 5529981254 7045421254 5524281594 5524252412 7045427224 5524282542 5529985248 5524250950 5524254500 5524254575 5524252005


This one does not work.

DECLARE @MyPhones as varchar(8000)
SELECT @MyPhones = @MyPhones + ' ' + [phonenum]
FROM srvmast

print @MyPhones

The next 2 examples do work.

Ex1

DECLARE @MyPhones as varchar(8000)
SELECT @MyPhones = ISNULL(@MyPhones+' ','') + [phonenum]
FROM srvmast

print @MyPhones

Ex2
DECLARE @MyPhones as varchar(8000)
SELECT @MyPhones = coalesce(@MyPhones+' ','') + [phonenum]
FROM srvmast

print @MyPhones


I could understand it not working if incountered a null but it doesn't.

Just trying to learn.

Thanks

Simi

 

[gmmastros]

DECLARE @MyPhones as varchar(8000)

-- At this point @MyPhones is NULL

SELECT @MyPhones = @MyPhones + ' ' + [phonenum]
FROM   srvmast

print @MyPhones

You declare the variable where it has a null value, and it remains that way for the life-time of the code.

DECLARE @MyPhones as varchar(8000)

-- At this point @MyPhones is NULL

Set @MyPhones = ''

-- Now that it has a value, you can start concatenating

SELECT @MyPhones = @MyPhones + ' ' + [phonenum]
FROM   srvmast

print @MyPhones

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian101]

I never would have thought of that...

Thanks

Simi