将sp_send_dbmail与单个sql行中的receivent和内容一起使用

Question

我有一个表格，里面填写了oldUserID、newUserID、姓名和电子邮件。我想对每行的电子邮件使用sp_send_dbmail。例如：

oldUserID|newUserID|名称|电子邮件

21213125 | 2355233571|汤姆|[e-mail]

65465465 | 4564884664 |垫子|[e-mail]

对200行以此类推。是否有任何方法可以向每行的电子邮件发送sp_send_dbmail，包括oldUserID和newUserID？邮件中的输出将类似于：

"您的旧用户id:21213125，您的新用户id:2355233571"

我希望不要手动输入每个电子邮件地址。

谢谢！

Answer 1

DECLARE
      @txt NVARCHAR(MAX)
    , @name NVARCHAR(60)
    , @email VARCHAR(100)
DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
    SELECT 'Your old user id: ' + CAST(oldUserID AS NVARCHAR(100)) 
         + ', your new user id: ' + CAST(newUserID AS NVARCHAR(100)), name, email
    FROM ...
OPEN cur
FETCH NEXT FROM cur INTO @txt, @name, @email
    WHILE @@fetch_status = 0
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail @profile_name = ...
                                   , @recipients = @email
                                   , @subject = @name
                                   , @body = @txt
                                   , @body_format = 'HTML'
    FETCH NEXT FROM cur INTO @txt, @name, @email
    END
CLOSE cur
DEALLOCATE cur