SELECT DISTINCT in DECLARE

[magmo]

Hi


I try to use this code in query analyzer

DECLARE @SendTo VarChar(4000)
SET @SendTo = ''
SELECT DISTINCT @SendTo = @SendTo + UserEmail + ';' FROM dbo.tbl_AccountInfo WHERE (UserEmail <> '')
PRINT @SendTo

The purpose of this code is to build up a ; seperated string of email adresses that I can use sending mail from SQL server.

It works but it only give me one record (should give me 130 records) , but if I remove the DISTINCT part it give me all records, duplicates too. Does anyone know why and how can I get this to work? Or maybe do it in another way?


Best regards

[skhanal]

DECLARE @SendTo VarChar(4000)
SET @SendTo = ''

SELECT @SendTo = @SendTo + a.UserEmail + ';'
FROM
(
SELECT DISTINCT UserEmail
FROM dbo.tbl_AccountInfo
WHERE (UserEmail <> '')) as a
PRINT @SendTo

[magmo]

Hi skhanal


Thanks a lot! That worked perfect, one thing though. The generated string ends up with a ;. Is there a way I can remove that to?


Best Regards

[Rawhide]

DECLARE @SendTo VarChar(4000)

SELECT @SendTo = IsNull(@SendTo + ';', '') + a.UserEmail
FROM
(
SELECT DISTINCT UserEmail
FROM dbo.tbl_AccountInfo
WHERE (UserEmail <> '')) as a

PRINT @SendTo

[magmo]

Hi


Worked like a charm, Thanks a lot!