Calling the sp_send_dbmail Stored Procedure
I am trying to create a Stored Procedure in SQL Server 2008 that sends emails that have been inserted into an outgoing table. I am using sp_send_dbmail
. It uses the cursor to loop through the outgoing table. I also want to delete records that contain emails that I have already sent.
The stored procedure seems to be blocking writes. This will prevent me from making a select statement on the outgoing table.
This is the main stored procedure code - submit issues.
Declare EmailCursor Cursor FAST_FORWARD FOR
select email_id, out_type, from_addr, to_addr,
reply_addr, subject, letter_body from outbound_email_queue
set @email_prof = (select email_profile from system_file)
Open EmailCursor
Fetch Next from EmailCursor into @email_type, @from_add, @to_add,
@reply_add, @Mysubject, @message
While @@FETCH_STATUS = 0 BEGIN
exec msdb.dbo.sp_send_dbmail
begin TRAN
DELETE FROM OUTBOUND_EMAIL_QUEUE WHERE EMAIL_ID = @email_id
if (@@error = 0)
begin
commit tran
end
else
begin
rollback tran
select -1
end
fetch next from emailcursor into.
end close emailcursor
deallocate emailcursor
end
source to share
I think it fires over the cursor. Also, your transaction with just the delete statement is unnecessary because a single delete operation is an atomic transaction. You can eliminate the cursor and use a try catch block to make sure your record is deleted only if the email was successfully sent.
While 1=1
BEGIN
select top 1 @email_id=email_id, @email_type=email_id, @from_add=from_addr, @to_add=to_addr, @reply_add=reply_addr, @Mysubject=subject, @message=letter_body
from outbound_email_queue
if @@ROWCOUNT = 0
break
begin TRAN
begin try
DELETE FROM outbound_email_queue WHERE EMAIL_ID = @email_id
exec msdb.dbo.sp_send_dbmail @recipients=@to_add ... etc.
commit tran
end try
begin catch
rollback tran
select -1
end catch
END
This code breaks the loop if an error is encountered or if it empties the outbound_email_queue table.
source to share