How to call sdb.dbo.sp_send_dbmail for each row of a sql result set

Question

I have a SQL Server table called EMails which consists of the columns

• Id int,
• Subject nvarchar(100)
• Body nvarchar(MAX),
• Recipient nvarchar(100)
• IsSent bit

My application populates it.

I want to use SQL Server to send the email.

I know how to send an email with

EXEC msdb.dbo.sp_send_dbmail

However the query

SELECT Subject,Body,Recipient FROM EMails
WHERE IsSent=0

can return up to multiple

I also want to SetIsSent to 1 after the mail has been sent.

Question:

1. Is it possible using a scheduled SQL server job. If so can you provide a code example?

2. Is it recommended for sending up to 50 emails max when the query is put in a sql server job that runs every 15 minutes

Answer 1

DECLARE @Subject NVARCHAR(100),
        @Body NVARCHAR(MAX),
        @Recipient NVARCHAR(100),
        @ID INT,
        @MailID INT

DECLARE @mails TABLE (ID INT, MailID INT)

DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
    SELECT ID, [Subject], Body, Recipient
    FROM dbo.EMails
    WHERE IsSent = 0

OPEN cur

FETCH NEXT FROM cur INTO @ID, @Subject, @Body, @Recipient

WHILE @@FETCH_STATUS = 0 BEGIN

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = '....',
        @recipients   = @Recipient,
        @subject      = @Subject,
        @body         = @Body,
        @mailitem_id  = @MailID OUTPUT

    INSERT INTO @mails (ID, MailID)
    VALUES (@ID, @MailID)

    FETCH NEXT FROM cur INTO @ID, @Subject, @Body, @Recipient

END

CLOSE cur
DEALLOCATE cur

WAITFOR DELAY '00:00:01'

UPDATE u
SET IsSent = 1
FROM dbo.EMails u
JOIN @mails m ON u.ID = m.ID
JOIN msdb.dbo.sysmail_mailitems s ON m.MailID = s.mailitem_id
WHERE s.sent_status = 1

-- failed mails

SELECT s.recipients, s.[subject], s.body, s.sent_status
FROM @mails m
JOIN msdb.dbo.sysmail_mailitems s ON m.MailID = s.mailitem_id
WHERE s.sent_status != 1