时间戳

首页 > 解决方案 > EXEC msdb.dbo.sp_send_dbmail 忽略查询

sql-server - EXEC msdb.dbo.sp_send_dbmail 忽略查询

问题描述

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

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.'

标签： sql-servertsqlsp-send-dbmail

解决方案

您在批处理顶部的查询与您执行的语句无关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。）

推荐阅读