CODEWITHSUNDEEP
sqlstringt-sqlstored-proceduresvarchar
Uğur Gümüşhan
Uğur Gümüşhan

Reputation: 2543

Making a long string from each row

I am trying to make a long string from comments and I created this sp

ALTER FUNCTION ugurcode.comment_summary
    (
    @opinionid int
    )
RETURNS nvarchar(max)
AS
    BEGIN
    declare @cs nvarchar(max);
    select @cs+=comment+'\n' from fev_comment where opinionid=@opinionid
    RETURN @cs/* value */
    END

this returns null, how else can I achieve this?

Upvotes: 1

Views: 51

Answers (1)

Martin Smith
Martin Smith

Reputation: 453940

Replace

declare @cs nvarchar(max);

with 

declare @cs nvarchar(max) = '';

Concatenating NULL returns NULL.

BTW: Microsoft say "The correct behavior for an aggregate concatenation query is undefined." and this approach can break. You might want to look at XML PATH instead (See Concatenating Row Values in Transact-SQL).

Upvotes: 3

Related Questions