Creating Stored procedure to send email to multipl

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)

Creating Stored procedure to send email to multipl

Author

Topic

madgts4
Starting Member

1 Post

Posted - 2007-09-20 : 08:00:23

Hi Everybody,

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

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

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-20 : 08:04:25

Select into a temp table with an identity ten loop through the identity

declare @id int
select @id = 0
while @id < (select max(id) from #tbl)
begin
select top 1 @id = id, @col1 = col1, @col2 = col2, ... from #tbl where id > @id order by id
-- do processing here
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources