COALESCE Question - 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

SQL Server 2000 Forums

SQL Server Development (2000)

COALESCE Question

Author

Topic

ackweb
Yak Posting Veteran

54 Posts

Posted - 2004-04-29 : 13:00:32

I've using COALESCE to combine the corresponding fields of several records into a CSV parameter in the following manner:

<code>
DECLARE @FieldOne varchar(2000)
DECLARE @FieldTwo varchar(200)

SELECT @FieldOne = COALESCE(@FieldOne + ', ', '') + FieldOne,
@FieldTwo = COALESCE(@FieldTwo + ', ', '') + FieldTwo

FROM ExampleTable

WHERE ID IN (1,2,3,4,5)

GROUP BY FieldOne, FieldTwo
SELECT @FieldOne, @FieldTwo
</code>

This statement successfully combines the fields for @FieldTwo, but the @FieldOne string parameter is cut off at 256 characters. I've declared @FieldOne as varchar(2000) and my understanding is that COALESCE will support up to 8000 characters. What am I doing wrong? I'd appreciate any insights on this problem. Thanks!

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-04-29 : 14:13:36

why are you using COALESCE? This function returns the first non-null value in an expression. Is that what you intended? Check BOL for details.

Post some DDL and sample data from your ExampleTable and we can help you out.

-ec

ackweb
Yak Posting Veteran

54 Posts

Posted - 2004-04-29 : 15:04:04

I've added a third integer field and my ExampleTable data is as follows:

ID FieldOne FieldTwo FieldThree
1 'Bob' 'Smith' 3
2 'Bill' 'Jones' 4
3 'Ben' 'Miller' 5

Using the modified code below, the desired output for the three parameters is as follows:

@FieldOne = Bob, Bill, Ben
@FieldTwo = Smith, Jones, Miller
@FieldThree = 12

With the exception of @FieldOne being cut off at 256 characters, this code returns the desired output. This approach was suggested in the following article:
http://www.sqlteam.com/item.asp?ItemID=2368

<code>
DECLARE @FieldOne varchar(2000)
DECLARE @FieldTwo varchar(200)
DECLARE @FieldThree int

SELECT @FieldOne = COALESCE(@FieldOne + ', ', '') + FieldOne,
@FieldTwo = COALESCE(@FieldTwo + ', ', '') + FieldTwo,
@FieldThree = COALESCE(@FieldThree + ' + ', '') + FieldThree

FROM ExampleTable

WHERE ID IN (1,2,3)

GROUP BY FieldOne, FieldTwo, FieldThree
SELECT @FieldOne, @FieldTwo, @FieldThree
</code>

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-30 : 14:24:10

Are you viewing this output in Query Analyzer? By default, QA displays only 256 characters of a varchar column, you can increase it from the Results tab in the options.

OS

ackweb
Yak Posting Veteran

54 Posts

Posted - 2004-05-01 : 00:28:19

That was the problem. Thank you for advising me of this default Query Analyzer setting and how to change it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources