调用sp_send_dbmail的存储过程-Java 学习之路

我'm trying to create a stored procedure in SQL Server 2008 that sends emails, that were inserted into an outbound table. I'm使用 sp_send_dbmail . 它已经发送了's using a cursor to loop thru the outbound table. I also want to delete the records that contain the emails I' .

存储过程似乎是锁定记录 . 它不会让我在出站表上做一个select语句 .

这是存储过程的基本代码 - 发布时遇到问题 .

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

1 回答

我认为它正在绊倒光标 . 此外，您的事务只是删除语句是不必要的，因为单个删除语句是原子事务 . 您可以消除光标并使用try catch块来确保仅在成功发送电子邮件时删除您的记录 .

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

如果遇到错误或者清空outbound_email_queue表，此代码将中断循环 .

回复于 2024-04-30T14:57:23+08:00

调用sp_send_dbmail的存储过程

1 回答

相关问题