using COALESCE retruns NULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

using COALESCE retruns NULL

Author

Topic

sagitariusmzi
Posting Yak Master

113 Posts

Posted - 2009-02-13 : 01:07:39

Hi, i have the problem is this query

DECLARE @List varchar(500)
DECLARE @nos VARCHAR(500)
SELECT @nos = COALESCE(@List + ', ', '') + CAST(sno_no AS varchar(5))
FROM Person.VIEWPerson
WHERE personid = '123'
ORDER BY personno
SELECT @List

This Query returns me Null

while

SELECT sno_no
FROM Person.VIEWPerson
WHERE personid = '123'
ORDER BY personno

1
2
3
4
5
6

i wants the results in 1,2,3,4,5,6 etc

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-13 : 01:13:06

here @list in no initialized so that select @list is showing u null

check select @nos

k u requierement is to get the sno_no is in comma separted
for this use xml path()

SELECT stuff((select ','+ CAST(sno_no AS varchar(64))
FROM Person.VIEWPerson
WHERE personid = '123'
ORDER BY personno for xml path('')),1,1,'')

sagitariusmzi
Posting Yak Master

113 Posts

Posted - 2009-02-13 : 01:18:59

ohh , yes i got
below is the changed query

DECLARE @List varchar(500)
DECLARE @nos VARCHAR(500)
SELECT @List = COALESCE(@List + ', ', '') + CAST(sno_no AS varchar(5))
FROM Person.VIEWPerson
WHERE personid = '123'
ORDER BY personno
SELECT @List

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 01:20:05

quote:
Originally posted by sagitariusmzi

Hi, i have the problem is this query

DECLARE @List varchar(500)
DECLARE @nos VARCHAR(500)
SELECT @nos = COALESCE(@List + ', ', '') + CAST(sno_no AS varchar(5))
FROM Person.VIEWPerson
WHERE personid = '123'
ORDER BY personno
SELECT @List

This Query returns me Null

while

SELECT sno_no
FROM Person.VIEWPerson
WHERE personid = '123'
ORDER BY personno

1
2
3
4
5
6

i wants the results in 1,2,3,4,5,6 etc

you dont need extra variable at all. just use this

DECLARE @nos VARCHAR(500)
SELECT @nos = COALESCE(@nos + ', ', '') + CAST(sno_no AS varchar(5))
FROM Person.VIEWPerson
WHERE personid = '123'
ORDER BY personno
SELECT @nos

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources