Using a loop to create a list of emails

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)

Using a loop to create a list of emails

Author

Topic

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-05-29 : 13:53:51

I have a stored proc I am running, and I would like to create a list of email addresses from a table and put that list into a variable. I did a basic while loop to work on syntax, but now I don't know how to actually get each address added on. Here's how I started it

declare @start int, @testEmail nvarchar(2000)

set @start = 1
set @testEmail = NULL
while @start <= (Select count(PADM_Email) from PADM_Emails)
Begin
--Print @start
set @testEmail = @testEmail + (Select distinct PADM_Email from PADM_Emails) + ';'
set @start = @start + 1
End

I know that the above is wrong, but I don't know how to get it right. Ideally, I want the @testEmail to look like this:

emailaddress1;emailaddress2;emailaddress3;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-29 : 13:57:52

Check out the CSV article here, you'd just use a semi-colon as your delimiter.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 14:02:46

May be this:-

declare @pk int, @testEmail nvarchar(2000)


select @pk=min(pk)
from YourTable

while @pk is not null
Begin
select @testEmail = coalesce(@testEmail + ';','') + email
from YourTable
where pk=@pk

select @pk=min(pk)
from YourTable
where pk>@pk
End
select @testEmail

where pk is your primary key of table.

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-05-29 : 14:14:38

Thank you - thats perfect :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 15:11:52

quote:
Originally posted by bmahony993

Thank you - thats perfect :)

You're welcome

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-30 : 00:11:11

Here's a faster way.


Declare @testEmail nvarchar(2000)


declare @table table(val varchar(50))
insert @Table(Val)
select 'Vinnie1@yahoo.com'
union all
select 'Vinnie2@yahoo.com'
union all
select 'Vinnie3@yahoo.com'
union all
select 'Vinnie4@yahoo.com'
union all
select 'Vinnie5@yahoo.com'
union all
select 'Vinnie6@yahoo.com'
union all
select 'Vinnie7@yahoo.com'
union all
select 'Vinnie8@yahoo.com'

select @testEmail = coalesce(@testEmail + ';','') + val
from @Table

select @testEmail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 01:41:18

or even new FOR XML feature in sql 2005

Declare @testEmail nvarchar(2000)


declare @table table(val varchar(50))
insert @Table(Val)
select 'Vinnie1@yahoo.com'
union all
select 'Vinnie2@yahoo.com'
union all
select 'Vinnie3@yahoo.com'
union all
select 'Vinnie4@yahoo.com'
union all
select 'Vinnie5@yahoo.com'
union all
select 'Vinnie6@yahoo.com'
union all
select 'Vinnie7@yahoo.com'
union all
select 'Vinnie8@yahoo.com'

SELECT LEFT(el.emaillist,LEN(el.emaillist)-1)
FROM (SELECT val+ ';' as [text()]
      FROM @table
      FOR XML PATH(''))el(emaillist)

output
---------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------
Vinnie1@yahoo.com;Vinnie2@yahoo.com;Vinnie3@yahoo.com;Vinnie4@yahoo.com;Vinnie5@yahoo.com;Vinnie6@yahoo.com;Vinnie7@yahoo.com;Vinnie8@yahoo.com

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-05-30 : 16:56:22

Here's a Microsoft only trick (Don't bother yelling at me!); it won't work for other vendors:

declare @results varchar(max)

set @results = '' -- empty string

UPDATE MyTable -- sic
SET @results = @results + email + ','

set @results = left(@results, len(@results) - 1) -- strip off the trailing comma (if desired)

Et voila!

=======================================
We should try to be the parents of our future rather than the offspring of our past. -Miguel de Unamuno

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources