group a recordset into a string for output

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 2005 Forums

Transact-SQL (2005)

group a recordset into a string for output

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2009-02-18 : 12:41:47

Hi all,

I want to join a record set into a string, then return it in an output parameter

ALTER PROCEDURE dbo.SP_Getwordlist
@domain nvarchar(50),
@keywordlists nvarchar(max) output
AS
SELECT @keywordlists = @keywordlists + (List_name + '#$#' + Convert(nvarchar(MAX),keyword) + '##$##')
FROM T_Keyword
where sitename=@domain
RETURN

which doesn't work. if only put
SELECT @keywordlists = (List_name + '#$#' + Convert(nvarchar(MAX),keyword) + '##$##')

it only returns last record.

any idea?

thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 12:46:58

quote:
Originally posted by mike13

Hi all,

I want to join a record set into a string, then return it in an output parameter

ALTER PROCEDURE dbo.SP_Getwordlist
@domain nvarchar(50),
@keywordlists nvarchar(max) output
AS
SELECT @keywordlists = COALESCE(@keywordlists,'') + (List_name + '#$#' + Convert(nvarchar(MAX),keyword) + '##$##')
FROM T_Keyword
where sitename=@domain
RETURN

which doesn't work. if only put
SELECT @keywordlists = (List_name + '#$#' + Convert(nvarchar(MAX),keyword) + '##$##')

it only returns last record.

any idea?

thanks a lot