TSQL function that returns a string of delimited emails - trying nested select

Question

I have a table with column email. I need to return all those emails in a string. I'm trying this code but getting:

Msg 102, Level 15, State 1, Procedure getemailjcp, Line 24
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure getemailjcp, Line 31
Incorrect syntax near 'a'.

TSQL function:

ALTER function [dbo].[getemails]
(
    @DB_Name varchar(100)
)
Returns varchar(4000)
AS
BEGIN
    DECLARE @out varchar (4000);
    DECLARE @in varchar (4000);
    SET @out =
        (
            SELECT @in = @in + email +'; ' 
            FROM 
                (
                    SELECT DISTINCT ISNULL(U.nvarchar4, 'NA') as email
                    FROM
                        sometable
            ) a
        ) b
    RETURN @out
END

score 1 · Answer 1 · edited May 23 '17 at 12:27

There is a lot about the subject in SO, here for example you will see several different solutions.

Correct comment from Vikdor. If your table has repeated email values and you do not want them and just concatenate distinct ones then use a nested query.

SELECT @in = COALESCE(@in, ';', '') + b.emails +'; ' 
  FROM (
        SELECT DISTINCT ISNULL(U.nvarchar4, 'NA') as emails FROM sometable
       ) b

Here you have working SQL Fiddle code for this case.

And if you want to really go deeper on this concatenation thing, here is a long detailed article on this subject.

Concatenating Row Values in Transact-SQL

In case the DISTINCT clause is no needed then no need to use nested select clauses. You just need to add the email column value to the variable you want to output.

For you case use this SELECT clause:

SELECT @in = COALESCE(@in + '; ', '') + ISNULL(U.nvarchar4, 'NA')
  FROM sometable

Check this SQL Fiddle working code.

If DISTINCT values need to be concatenated, then nested query is needed, right? — Vikdor, Sep 22 '12 at 11:05

score 0 · Answer 2 · answered Sep 22 '12 at 10:35

This would work (not sure where @DB_name is being used, btw):

ALTER function [dbo].[getemails]
(
    @DB_Name varchar(100)
)
Returns varchar(4000)
AS
BEGIN
    DECLARE @in varchar (4000);
    SELECT @in = ''
    SELECT @in = @in + email +'; ' 
    FROM 
        (
            SELECT DISTINCT ISNULL(U.nvarchar4, 'NA') as email FROM sometable
        ) b
    RETURN @in
END

score 0 · Answer 3 · answered Sep 22 '12 at 11:45

Try to use the following

CREATE function [dbo].[getemails] 
( 
    @DB_Name nvarchar(100) 
) 
Returns nvarchar(max) 
AS 
    BEGIN 
    DECLARE @in nvarchar (max); 
    SELECT @in = ISNULL(@in, '') + b.emails +'; '  
    FROM ( 
        SELECT DISTINCT ISNULL(U.nvarchar4, 'NA') as emails FROM sometable
       ) b
    RETURN @in 
END

TSQL function that returns a string of delimited emails - trying nested select

3 Answers3