exec msdb.dbo.sp_send_dbmail忽略查询

Question

我有一个代码向客户发送生日电子邮件，查询正常工作，但是SQL Mail Server总是向所有客户发送生日电子邮件，即使他没有生日

use Insurance
go

select 
  Customer.CustomerID
  ,Customer.FirstName
  ,Customer.LastName
  ,Customer.Birthday
  ,Customer.Email
from Customer
where Customer.CustomerID = Customer.CustomerID and 
                DAY([Birthday]) = DAY(GETDATE())
                AND MONTH([Birthday]) = MONTH(GETDATE())
declare @Receipientlist nvarchar(4000)
set @Receipientlist =
      STUFF((select ';' + Email FROM dbo.Customer FOR XML PATH('')),1,1,'')

EXEC msdb.dbo.sp_send_dbmail @profile_name='test',
    @recipients=@Receipientlist,
    @subject='Insurance',
    @body='Happy Birthday.
      Today is your Birthday.'

Answer 1

批次顶部的查询与执行msdb.dbo.sp_send_dbmail的语句无关。如果您只想给客户的生日发送给客户，则需要在创建收件人的语句中过滤(并且您不需要以前的语句(：

DECLARE @RecipientList nvarchar(4000);
--I removed the CustomerID = CustomerID clause, as it'll always equal itself,
--apart from when it's value is NULL (and I doubt it'll ever be NULL)
SET @RecipientList = STUFF((SELECT N';' + Email
                             FROM dbo.Customer
                             WHERE DAY([Birthday]) = DAY(GETDATE())
                               AND MONTH([Birthday]) = MONTH(GETDATE())
                             FOR XML PATH(N''),TYPE).value('.','nvarchar(4000)'), 1, 1,N'');
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'test',
                             @recipients = @RecipientList,
                             @subject = 'Insurance',
                             @body = 'Happy Birthday.
            Today is your Birthday.';

我还更改了返回子查询的值，使用的TYPE和value子句的方式。电子邮件地址可以包含一些特殊字符，而这些字符将在不使用TYPE的情况下逃脱(例如&成为&(。(我还更正了@RecipientList的拼写。(