Creating Stored Procedure To Send Email To Multiple Users

Hi Everybody,

I am trying to setup a stored procedure that runs through a Reminders table and sends an email to users based on DateSent field being smaller than todays date. I have already setup the stored procedure to send the email, just having trouble looping through the recordset.




Code Snippet


CREATE PROCEDURE [dbo].[hrDB_SendEmail]

AS

BEGIN


DECLARE @FirstName nvarchar(256),

@LastName nvarchar(256),

@To nvarchar(256),

@ToMgr nvarchar(256),

@Subject nvarchar(256),

@Msg nvarchar(256),

@DateToSend datetime,

@Sent nvarchar(256),

@ReminderID int,

@RowCount int,

@Today datetime,

@Result nvarchar(256)

-- Get the reminders to send


SELECT

@ReminderID = r.intReminderID,

@DateToSend = r.datDateToSend,

@FirstName = e.txtFirstName,

@LastName = e.txtLastName,

@To = e.txtEmail,

@Subject = t.txtReminderSubject,

@Sent = r.txtSent

FROM


(auto_reminders r INNER JOIN employee e ON r.intEmployeeID = e.intEmployeeID) INNER JOIN ref_reminders t ON r.intReminderType = t.intReminderTempID

WHERE


(((r.datDateToSend)<20/12/09) AND

((r.txtSent)='False'))

-- Send the Emails


WHILE(LEN(@To) > 0)

BEGIN


EXEC @Result = sp_send_cdosysmail @To, @ToMgr, @Subject, @Msg

END

-- Mark the records as sent

IF @Result = 'sp_OAGetErrorInfo'


BEGIN


SELECT @Sent = 'Error'

END
ELSE


BEGIN


SELECT @Sent = 'True'

END
UPDATE auto_reminders

SET


auto_reminders.txtSent = @Sent, auto_reminders.datDateSent = @Today

WHERE


intReminderID = @ReminderID

END

GO







From the code you can probably tell I am new to writing stored procedures, so I apologise for any obvious errors. My major problems are :-


how to loop through each record

how to get todays date

whether the struture of the procedure is correct
Also, if you think there is an easier way or a better method, please suggest it. I am open to any suggestions you may have,

Thanks in advance
Ben