将sp_send_dbmail与单个sql行的接收者和内容一起使用 [英] Using sp_send_dbmail with recieptent and content from single sql row
问题描述
我有一个表,其中填充了oldUserID,newUserID,名称和电子邮件.我想将sp_send_dbmail用于每一行的电子邮件.例如:
I have a table filled with oldUserID, newUserID, name and email. I want to use sp_send_dbmail to the email on each row. For example:
oldUserID | newUserID |名称|电子邮件
oldUserID | newUserID | name | email
21213125 | 2355233571 |汤姆| tom@gmail.com
21213125 | 2355233571 | Tom | tom@gmail.com
65465465 | 4564884664 |垫子| mat@gmail.com
65465465 | 4564884664 | Mat | mat@gmail.com
以此类推200行.有什么方法可以将sp_send_dbmail发送到每行的电子邮件,包括oldUserID和newUserID?邮件中的输出将类似于:
And so on for 200 rows. Is there any way to send an sp_send_dbmail to the email on each row including oldUserID and newUserID? The output in the mail would be something like:
您的旧用户ID:21213125,您的新用户ID:2355233571"
"Your old user id: 21213125, your new user id: 2355233571"
我希望不要手动输入每个电子邮件地址.
I would appreciate not to enter each emailadress manually.
谢谢!
推荐答案
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
这篇关于将sp_send_dbmail与单个sql行的接收者和内容一起使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!