Paduwan
asked on
SQL query to email customers
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?
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'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Paduwan,
Some amendment for @emailid:
@emailid varchar(100)
Some amendment for @emailid:
@emailid varchar(100)
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
ASKER
Thanks x-com that code works a treat!
set @emailid =
(SELECT rv_custom_incorrect_org.em
FROM rv_custom_incorrect_org
WHERE rv_custom_incorrect_org.lo
The subquery is returning multiple rows which cannot be assigned to a single variable. You need to use a cursor to handle this ..