simple looping concatenate thingy

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

Transact-SQL (2000)

simple looping concatenate thingy

Author

Topic

spudhead
Starting Member

34 Posts

Posted - 2002-10-23 : 10:39:46

Hi,

The code below is a segment of a stored proc. What I'd like it to do, instead of looping through all the email addresses and sending an email to each, is loop through all the emails and build up a semicolon-seperated list so I can send just one email at the end. It's just the "fetch next....into" bit that's confusing me, I dunno how to concatenate it when it's like that. Thanks for any help.


declare em_cursor1 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor1
fetch next from em_cursor1
into @em1
while @@FETCH_STATUS=0
begin
select @sbj1=@Origin+': '+@mySubject+' (ThreadID='+cast(@existingID as varchar)+')'
select @bdy1='User '+@Origin+' has replied to a thread on the test forum. The post reads:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@MessageText
exec master.dbo.xp_sendmail
@recipients=@em1,
@message=@bdy1,
@subject=@sbj1
fetch next from em_cursor1
into @em1
end
close em_cursor1
deallocate em_cursor1

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-23 : 10:41:12

How about:

http://www.sqlteam.com/item.asp?ItemID=11021

There's a few other methods too, search the site for "CSV".

spudhead
Starting Member

34 Posts

Posted - 2002-10-23 : 12:22:02

OK, cheers for that; I think I've got something. The following prints a list of email addresses when run on Query Analyser:
[code]
declare @emList varchar(8000)
declare @em1 varchar(100)
select @emList=''
declare em_cursor1 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor1
fetch next from em_cursor1 into @em1
while @@FETCH_STATUS=0
begin
SELECT @emList=@emList + @em1 + '; '
fetch next from em_cursor1 into @em1
end
close em_cursor1
deallocate em_cursor1
PRINT @emList
[code]

but when I put this into the stored proc (without the print bit), the job fails with the error "xp_sendmail: Could not resolve recipient" Why would that be?

It also has another error; "Associated statement is not prepared", but I think that's unrelated? (ie: We could fix the list of email addresses but still get that error, that's the error it usually complains about.)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-23 : 12:35:31

After the last email address in the list, there will be a semicolon on the end. This will fix it:

PRINT Left(@emList, Len(@emList)-1)

Use that expression to trim off the last character from the string.

The "associated statement" error could have something to do with the ending semicolon, because a semicolon is also a command separator in SQL, and the parser might think there's supposed to be another SQL statement after that semicolon.

spudhead
Starting Member

34 Posts

Posted - 2002-10-24 : 06:39:44

Ah, didn't think if that. Cheers :)

spudhead
Starting Member

34 Posts

Posted - 2002-10-24 : 11:58:39

Aaaagh. Another problem.

The list of email addresses is longer than 8000 characters. And apparently I can't declare local variables as text. How can I make myself a list of about 1000 email addresses to drop into the @recipients??

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-24 : 12:56:04

Use multiple varchar(8000) variables for recipients. I haven't tested this, but hopefully it will work:

DECLARE @recip1 varchar(8000), @recip2 varchar(8000), @recip3 varchar(8000)
--put in code to build @recip1, @recip2, and @recip3 lists
--watch for stray semicolons
xp_sendmail @recipients=@recip1 + @recip2 + @recip3,
--...blah blah blah add the other xp_sendmail parameters

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources