Solved

SQL query to email customers

Posted on 2009-03-31
5
354 Views
Last Modified: 2013-12-17
I'm trying to email customers in a table when TableA.Location value is NULL. Basically:

if TableA.Location IS NULL
then send an email to the value specified in TableA.Email_ID

Everything works if I have a single result from the query i.e. if only one record meets the argument then an email is sent. However it is not working for multiple results. I get the following error:

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

I'm new to SQL but it seems I need a IF THEN ELSE NEXT query or similar. Can anyone help please?

declare @newline varchar(10)
declare @body varchar(1000)
declare @footer varchar(100)
declare @emailid varchar(100)
 
	set @newline = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
 
	set @footer = 'To change your details please use the following link:' 
				+ @newline +
				'abc' + @newline +
				'123' + @newline +
				'xyz'
 
	set @body = 'The following details need to be updated:' + @newline 
 
		SELECT @body = @body + (rv_custom_incorrect_org.surname + @newline)  
 
		FROM rv_custom_incorrect_org
 
		WHERE rv_custom_incorrect_org.location_name IS NULL
 
	set @body = @body + @newline + @footer 
 
	set @emailid = 
 
		(SELECT rv_custom_incorrect_org.email_id
 
		FROM rv_custom_incorrect_org
 
		WHERE rv_custom_incorrect_org.location_name IS NULL)
 
EXEC msdb.dbo.sp_send_dbmail 
 
    @recipients = @emailid,
    @subject = 'Request to Update Your Customer Details',
    @body = @body,
    @body_format = 'TEXT'

Open in new window

0
Comment
Question by:Paduwan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 24036613
The segment below is the problem -

        set @emailid =
 
                (SELECT rv_custom_incorrect_org.email_id
 
                FROM rv_custom_incorrect_org
 
                WHERE rv_custom_incorrect_org.location_name IS NULL)

The subquery is returning multiple rows which cannot be assigned to a single variable. You need to use a cursor to handle this ..
0
 
LVL 29

Accepted Solution

by:
David H.H.Lee earned 125 total points
ID: 24036616
Hi Paduwan,
You need to declare CURSOR to manipulate multiple returned records in this case.
eg:

Amend These lines
=================
set @emailid =
 
                (SELECT rv_custom_incorrect_org.email_id
 
                FROM rv_custom_incorrect_org
 
                WHERE rv_custom_incorrect_org.location_name IS NULL)
 
EXEC msdb.dbo.sp_send_dbmail
 
    @recipients = @emailid,
    @subject = 'Request to Update Your Customer Details',
    @body = @body,
    @body_format = 'TEXT'

To
========
declare
 @acursor cursor,
 @emailid int
 
set @acursor = cursor for SELECT rv_custom_incorrect_org.email_id FROM rv_custom_incorrect_org WHERE rv_custom_incorrect_org.location_name IS NULL

open @acursor
fetch next from @acursor into @emailid
while (@@fetch_status = 0)
begin
 --do something with @emailid variable
 EXEC msdb.dbo.sp_send_dbmail
    @recipients = @emailid,
    @subject = 'Request to Update Your Customer Details',
    @body = @body,
    @body_format = 'TEXT'

 fetch next from @acursor into @emailid
end
 
close @acursor
deallocate @acursor


0
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 24036629
Hi Paduwan,
Some amendment for @emailid:
@emailid varchar(100)
0
 
LVL 25

Expert Comment

by:reb73
ID: 24036649
Try something like -
declare @newline varchar(10)
declare @body varchar(1000)
declare @footer varchar(100)
declare @emailid varchar(100)
declare @surname varchar(100)
 
        set @newline = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
 
        set @footer = 'To change your details please use the following link:' 
                                + @newline +
                                'abc' + @newline +
                                '123' + @newline +
                                'xyz'
 
declare mycurs insensitive cursor for
	select surname, email_id
	from rv_custom_incorrect_org
	where email_id > ''
	for read only
 
open mycurs
fetch next from mycurs into @surname, @emailid
while (@@fetch_status = 0)
begin
 
        set @body = 'The following details need to be updated:' + @newline + @surname
		    + @newline + @newline + @footer 
 
	EXEC msdb.dbo.sp_send_dbmail 
	    @recipients = @emailid,
	    @subject = 'Request to Update Your Customer Details',
	    @body = @body,
	    @body_format = 'TEXT'
 
        fetch next from mycurs into @surname, @emailid
end
close mycurs
deallocate mycurs

Open in new window

0
 

Author Closing Comment

by:Paduwan
ID: 31565181
Thanks x-com that code works a treat!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
The video tutorial explains the basics of the Exchange server Database Availability groups. The components of this video include: 1. Automatic Failover 2. Failover Clustering 3. Active Manager
The basic steps you have just learned will be implemented in this video. The basic steps are shown to configure an Exchange DAG in a live working Exchange Server Environment and manage the same (Exchange Server 2010 Software is used in a Windows Ser…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question