问如何为sql结果集的每一行调用sdb.dbo.sp_send_dbmail

EN

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