sql-分别从表格发送同一邮件给多个用户

Question

我想使用 sp_send_dbmail 分别从表中发送相同的邮件到从表格中获取的多个电子邮件ID。如果我使用以下查询，则该邮件将被发送给"到"部分中的所有用户。但是我的要求是单独发送邮件。

DECLARE @Receipientlist varchar(8000)  
SET @ReceipientList = STUFF((SELECT ';' + emailaddress FROM Your query here 
FOR XML PATH('')),1,1,'')

EXEC sp_send_dbmail  @profile_name= your email profile in db,  
@recipients= @Receipientlist,  
@subject='your subject here',  
@body='body message here'  

请建议一些解决方案。

Answer 1

定义光标并尝试;

    declare @emailaddress nvarchar(250)
    DECLARE db_cursor CURSOR FOR  
    SELECT emailaddress FROM Your query here  
    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @emailaddress   
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
            EXEC sp_send_dbmail  @profile_name= your email profile in db,  
            @recipients= @emailaddress,  
            @subject='your subject here',  
            @body='body message here' 
           FETCH NEXT FROM db_cursor INTO @emailaddress   
    END  
CLOSE db_cursor   
DEALLOCATE db_cursor