Concatenating multiple rows into single values, Storing a dynamic returned value in a variable

Input :

1 a@a.com

2 b@b.com

3 c@c.com

Output:

a@a.com, b@b.com, c@c.com

/*

SELECT

LEFT(Emails,LEN(Emails)-1)

FROM (

SELECT TOP 1

(

SELECT

UserName + ', ' as [text()]

FROM Members

WHERE ID in (1,2,3)

ORDER BY UserName ASC FOR XML PATH('')

) AS Emails ) AS ME*/

ALTER PROCEDURE [dbo].[Comments_SelectByIds4ReplyMail]

(

@Ids NVARCHAR(MAX)

)

AS

SET NOCOUNT ON

DECLARE @Sql NVARCHAR(MAX)

DECLARE @Params NVARCHAR(30)

DECLARE @EmailIds NVARCHAR(MAX)

SET @Params = '@val NVARCHAR(MAX) OUTPUT'

SET @Sql = 'SELECT @val=LEFT(Emails,LEN(Emails)-1) '

SET @Sql = @Sql + ' FROM '

SET @Sql = @Sql + '(SELECT TOP 1 ( SELECT UserName + '', '' as [text()] FROM Members WHERE ID in '

SET @Sql = @Sql + '('+@Ids + ') ORDER BY UserName ASC FOR XML PATH('''') ) AS Emails  ) AS ME'

Exec sp_executesql @Sql,@Params, @val=@EmailIds OUTPUT

SELECT @EmailIds AS EmailIds