Prev: Remove characters in an ASCII decimal range: How To?
Next: BUY FAKE PASSPORTS OF AUSTRALIA,FAKE AUSTRALIAN PASSPORTS FOR SALE
From: Rita on 18 May 2010 08:10
This is my stored procedute to send an email to multiple recipients,
Its working if there is only one recipient, But how can i send email
to multiples?
GO
ALTER PROCEDURE aspdnsf_sendCouponCode
AS
BEGIN
declare @bodyContent varchar(3000)
declare @couponcode nvarchar(50)
declare @email varchar(300)
DECLARE @emailCount int
BEGIN
SELECT @email = Email from (SELECT Email from Customer WHERE
MONTH(GETDATE()) = MONTH(DateOfBirth)) AS Email
SELECT @couponcode = CouponCode from (SELECT CouponCode from Coupon
WHERE MONTH(GETDATE()) = MONTH(CreatedOn)) AS Code
SET @bodyContent = 'You can use ' + @couponcode + ' code to purchase
products in ilovetocreate.com.'

EXEC msdb..sp_send_dbmail @profile_name='Profile',
@recipients = @email,
@subject = 'Message from WEB - Use this Coupon code',
@body = @bodyContent;
END
END

This query(SELECT Email from Customer WHERE MONTH(GETDATE()) =
MONTH(DateOfBirth)) returns 3 records, But its sending email only to
the first recipient and not for other recipients.

Please help me with this stored procedure.
From: Plamen Ratchev on 18 May 2010 12:01
You have to concatenate the emails separated by semi-colon. Here is
example using FOR XML PATH:

SET @email = (SELECT Email + ';'
FROM Customer
WHERE DateOfBirth >= DATEADD(MONTH, DATEDIFF(MONTH, 0,
CURRENT_TIMESTAMP), 0)
AND DateOfBirth < DATEADD(MONTH, DATEDIFF(MONTH, 0,
CURRENT_TIMESTAMP) + 1, 0)
FOR XML PATH(''));

--
Plamen Ratchev
http://www.SQLStudio.com
From: sloan on 19 May 2010 10:04

I love this trick Plamen. I've been using it for (a long time, over 2 years
at least) since I saw one of your posts about it.

I just wrote up a delimited email list query last week actually....using
this.



"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:bce5v5l10oiquj22t54h86pvo5a2r8f5m2(a)4ax.com...
> You have to concatenate the emails separated by semi-colon. Here is
> example using FOR XML PATH:
>
> SET @email = (SELECT Email + ';'
> FROM Customer
> WHERE DateOfBirth >= DATEADD(MONTH, DATEDIFF(MONTH, 0,
> CURRENT_TIMESTAMP), 0)
> AND DateOfBirth < DATEADD(MONTH, DATEDIFF(MONTH, 0,
> CURRENT_TIMESTAMP) + 1, 0)
> FOR XML PATH(''));
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


 | 
Pages: 1
Prev: Remove characters in an ASCII decimal range: How To?
Next: BUY FAKE PASSPORTS OF AUSTRALIA,FAKE AUSTRALIAN PASSPORTS FOR SALE