Sending Email with Cursor

[Quehay]

Funny that the most recent thread is about xp_SendMail...

I've got xp_sendmail working, and it's wrapped up in a proc that only asks for the "TO:" as a parameter (the other mail fields are hard coded). I want to periodically run a job that goes through a cursor of email addresses (cursor calls a view that already does set logic to produce a restricted number of records) and runs the mail procedure.

The code below works well with just PRINT and the mail command commented out--it gives a list of each recipient in the results window. However, when I actually use the email procedure it sends an email to the first recipient in the cursor as many times as there are records in the cursor (fortunately I'm the test recipient and there are only 3 email addresses in the test table).

I can't imagine that there is a threading issue here. Any hints would be much appreciated!

[tt]CREATE procedure usp_NotifySomebody
as

Declare @Email varchar(100)
Declare @intResult integer
Declare curEMailList cursor

for
select email
from temp
for read only

open curEMailList

Fetch Next From curEMailList
Into @Email


While (@@FETCH_STATUS = 0)

Begin
exec usp_SendEmail @email
print @email
Fetch Next From curEMailList
Into @Email

end

Close curEMailList
Deallocate curEMailList

[/tt]

 

[osjohnm]

Hi try this instead:

CREATE procedure usp_NotifySomebody
as

Declare @Email varchar(100)
Declare @intResult integer
Declare curEMailList cursor for

select email
from temp
for read only

open curEMailList

Fetch curEMailList Into @Email


While (@@FETCH_STATUS = 0)

Begin
exec usp_SendEmail @email
print @email
Fetch curEMailList Into @Email
End

Close curEMailList
Deallocate curEMailList

John

 

[Quehay]

Please ignore. I discovered that I had ALL the parameters hard-coded in the email proc (i.e. I was sending it to me by design). Just proves the law of the universe wherein as soon as you expose your stupidity to others it becomes obvious to you as well. ;-)