How to use concatenated multiple rows of TEXT to update another TEXT column in SQL Server 2000?

I have a problem I'm trying to solve and a solut开发者_开发知识库ion is not readily apparent. Since I cannot use temporary variables of type Text, I am having some trouble getting this figured out.

First table (DocumentChunks) has two columns - DocumentID (int - foreign key) and TextChunk (Text)

Second table (Document) has many columns including DocumentID (int - primary key) and DocumentText (Text)

Document <-> DocumentChunks is a one-to-many relationship.

I need to concat all the TextChunk values together with a carriage return line feed after each, and use that to update the corresponding DocumentText column in the Document table.

I've seen plenty of example using temporary variables, but I can't use them.

All suggestions are appreciated!

Well you could attempt a scalar function. Use this in your update query like:

SET DocumentText=fn_ConcatTextChunks(Document.DocumentID)

The only problem is that you can't use a text return type or local variables in SQL Server 2000 in a scalar function. So if your data is too large, this won't work.

CREATE FUNCTION [dbo].[fn_ContatTextChunks]
(
    @DocumentID int
)
RETURNS varchar(8000)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varchar(8000)

    SELECT @Result = COALESCE(@Result + '\n', '') + CAST(dc.TextChunk As varchar) 
    FROM DocumentChunks dc
    WHERE dc.DocumentID=@DocumentID

    -- Return the result of the function    
    IF @Result IS NULL BEGIN
        SET @Result = ''
    END 
    RETURN @Result
END

but I suppose it's worth a shot.