子查询返回超过 1 个值 [英] Subquery returned more than 1 value

问题描述

我需要在 SQL Server 2008 中设置一个作业，以便在月初运行，向我们的客户发送电子邮件.但是，我不知道如何循环遍历子查询的结果.

I need to set up a job in SQL Server 2008 to run at the first of the month sending an email to our customers. However, I do not know how to loop through the results of the subquery.

导致此错误消息:

子查询返回超过 1 个值.这是不允许的，当子查询遵循 =, !=, <, <= , >, >=或者当子查询被用作表达.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

以下是相关代码:

SET @Recipients =(SELECT DISTINCT a.EMail
         FROM   a
        --approximately 600 email addresses 

SET @MailSubject = 'Customer News' 
SET @MailRecipients = @Recipients
SET @MailMessage =  'Dear customer, Attached is your customer news letter.'

SET @FileName = N'E:file	oeattached.doc'

EXEC msdb.dbo.sp_send_dbmail @recipients = @MailRecipients, 
    @body = @MailMessage,
    @blind_copy_recipients='[e-mail]', 
    @subject = @MailSubject,
    @file_attachments  = @FileName

推荐答案

错误出现在这里，您有很多行试图分配给单个变量

The error is here where you have many rows trying to be assigned to a single variable

SET @Recipients =(SELECT DISTINCT a.EMail
     FROM   a
    --approximately 600 email addresses 

因此您需要将其更改为单独的列表

You'd need to change it to a separated list thus

SET @Recipients = STUFF(
           (select DISTINCT ';' + CAST(a.EMail AS varchar(max))
           FROM a FOR XML PATH ('')
           )
          ,1,1, '') 

注意:@Recipients 需要为 varchar(max)

Note: @Recipients will need to be varchar(max)

这篇关于子查询返回超过 1 个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！