where clause loop email

[dtrivedi]

This query run fine. it sends me the emails that i need.

here is the query:


use ANALYTICS

create table #ezpayloc
(locations varchar(50), storeemail varchar(50))



insert into #ezpayloc SELECT distinct [LOCATION], +('mac'+(location)+'@maclocal.com')
FROM vw_PAST_DUE_LESSONS_EZPAY] order by location

select * from #ezpayloc



SELECT distinct [LOCATION], +('mac'+(location)+'@maclocal.com')
FROM vw_PAST_DUE_LESSONS_EZPAY] order by location
GO

DECLARE @locs varchar(max)
declare @email varchar(max)

declare t_ezpay
cursor for select storeemail from #ezpayloc
declare t_ezpay
cursor for select locations from #ezpayloc
open t_ezpay

fetch next from t_ezpay into @email

fetch next from t_ezpay into @locs

while @@FETCH_STATUS = 0
BEGIN

Print 'Processing: ' + @email

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'dtrivedi@musicarts.com',
@body= 'All attempts to charge the credit card on file for the following EZ Pay Customers has failed.
Please contact the customer, update the card on file and charge the balance to the new card.',
@query='SELECT [LOCATION],[CUSTOMER],[ACCOUNT],[CUSTOMER_NAME],[PHONE]
FROM vw_PAST_DUE_LESSONS_EZPAY]' ,
@subject = 'EZ Pay'


fetch next from t_ezpay
into @email

END

close t_ezpay
deallocate t_ezpay

drop table #ezpayloc

now what i need it to do is with each time the query fetches i need it to take the results and email it to the location specific to it

for ex if it runs
location cutomer
0011 1234
0011 1251
0011 4250
0012 1506
0012 1508

so when it send an email to location 0011, location 0011 should only get their info as it the first 3 results

does that make sense?

 

[mrdenny]

You would need to run the EXEC msdb.dbo.sp_send_dbmail command in an inner cursor against the distinct list of locations so that you can then filter out the in the sp_send_Dbmail against that location code so that olny the correct records are sent.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog