Stored Procedure for every record in a table

[MikeMcKeown]

I would like to run a store procedure for every record in a table.

Go to the first record
send an email
Go to the second record

and so on until every record has has an email sent.

I was just wondering how you do this in a SP, currently the SP only does it for the one record

Thanks in advance.

 

[TalentedFool]

I've done the same as what you've done and I used a cursor

I assume you're only using a few fields from your tables?
Maybe a name and email address?

create proc sp_sendemails as

declare @name char(howeverbig)
declare @email char(howeverbig)
declare get_row cursor for
select name,email from table
open get_row
fetch next from get_row into @name,email
while (@@fetch_status = 0)
begin
send email here
fetch next from get_row into @name,email
end
close get_row
deallocate get_row


Hope that helps



~ Remember - Nothing is Fool Proof to a Talented Fool ~

 

[SQLSister]

Be aware that if you have a lot of records this will take a lot of processing power and time. Unfortunately, it's the only way I know of to do this from within SQL Server.

But I use a a mail program and import my table data into it and run this sort of thing from my pc to avoid keeping my SQL Server tied up with this type of processing. A quick google search will get you a list of free mail servers you can do this with. I picked one called Group Mail and it seems to work well.

Questions about posting. See faq183-874

 

[MikeMcKeown]

i have decided to use a cursor as it is unlikey to be anymore than 10 records that the cursor will be looping through.

Thanks