Comma Delimited String - 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)

Comma Delimited String

Author

Topic

Dennis Falls
Starting Member

41 Posts

Posted - 2008-02-04 : 12:39:28

I have found numerous articles on how to concatenate a comma delimited string, including the scalar UDF, temp tables and coalesce, but I have not found any that describes how to return a value greater than 8000 characters.

I have a table with 3 fields:
CaseNumber_fk Int
SeqNum Int
Notes Varchar(4000)

I need a view that returns Distinct CaseNumber_fk with all the notes concatenated into 1 record. I have created a UDF as follows:

create FUNCTION dbo.ConcatCPCnotes(@VID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''
SELECT @Output = CASE @Output
WHEN '' THEN notes
ELSE @Output + ', ' + notes
END
FROM tbl_cpc_notes_stage
WHERE CaseNumber_fk = @VID
ORDER BY casenumber_fk, seqnum
RETURN @Output
END

But as expected, this will only return the first 8000 characters.

If anyone has a suggestion on how to return a value greater than 8000 characters, I would appreciate hearing them

georgev
Posting Yak Master

122 Posts

Posted - 2008-02-05 : 10:01:38

You can't. varchar(8000) is your limit!

A datapage can only hold 8kb of data and this cannot be exceeded in SQL Server 2000
If you were to hop onto 2005 you'd find varchar(MAX) your solution.

George
<3Engaged!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 10:09:18

else you need to go for a text field which is a bit of pain to work with as it dont support too many functions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources