LOOP QUERY HELLLP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

LOOP QUERY HELLLP

Author

Topic

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-19 : 15:29:31

I need to create a query which emails each store their location information. So it's one email which has the store location information which is going to 37 indicidual stores with there own information.

i think this is called email looping? am new at this so not too sure.
so far i have the scrip to generate the email of the query results to email addres

PLEASE HELP

BELWO IS THE QUERY THAT I HAVE

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'dtrivedi@musicarts.com',
@body= 'BODY OF THE EMAIL'
@query='SELECT [LOCATION],[CUSTOMER],[ACCOUNT],[CUSTOMER_NAME],[PHONE]
FROM vw_PAST_DUE_LESSONS_EZPAY]' ,
@subject = 'EZ',

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-19 : 15:34:10

You'd have to send one email per store then. Is there a table that can be queried to get each store?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-19 : 15:35:11

yes i created at temp table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-19 : 15:36:25

Show us some sample data then and show us the different queries that would need to be run for @query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-19 : 15:41:34

loc cname
1 adam
1 dana
2 doyel
2 brian
2 april
3 brent
3 amber
4 ryan
4 nick

so this is just sample data i have much more data to send
but what i basically need it to do is
runa a query for each store select location then find the cname for that location and then email that query to that specific store

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-19 : 15:43:24

What contains the email addresses? And how will @query look for each store? Ie, what will be the difference in the query?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-19 : 15:44:51

this exec statement will have the email
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'dtrivedi@musicarts.com',
@body= 'BODY OF THE EMAIL'
@query='SELECT [LOCATION],[CUSTOMER],[ACCOUNT],[CUSTOMER_NAME],[PHONE]
FROM vw_PAST_DUE_LESSONS_EZPAY]' ,
@subject = 'EZ',

the diffrence will be CNAME for each location

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-19 : 15:48:03

I don't think you are following me. You have to change the @recipients variable in order to email different people. So where can the @recipients data values be read from?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-19 : 15:48:40

ohh i thought i can put that manually in there...if nto i guess i should add them to my temp file??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-19 : 15:50:17

If it can be done manually, then why do you need it to loop?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-19 : 15:51:40

sorry am new at this...ok so im adding the location emails to the temp location file

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-19 : 15:53:59

Here's some pseudocode:

CREATE TABLE #temp (id int identity(1,1), ...)

INSERT INTO #temp...

DECLARE @cname varchar(50), @email varchar(100), @id int

SELECT @id = MIN(id) FROM #temp

SELECT @cname = cname, @email = email
FROM #temp
WHERE id = @id

WHILE @id <= (SELECT MAX(id) FROM #temp)
BEGIN
DoEmail
SET @id = @id + 1
END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-19 : 16:00:23

i'm going to finish entering the emails and then try this
lets hope it works
thanks alot

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-19 : 16:15:22

ok am done entering emails now am going to try this code...can you tell me what @id int is? and how will it send the data from teh other query???

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-19 : 16:15:55

send these query results to those locations and emails

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'dtrivedi@musicarts.com',
@body= 'BODY OF THE EMAIL'
@query='SELECT [LOCATION],[CUSTOMER],[ACCOUNT],[CUSTOMER_NAME],[PHONE]
FROM vw_PAST_DUE_LESSONS_EZPAY]' ,
@subject = 'EZ',

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-19 : 16:26:41

i am getting this error
i dont know what it means

Must declare the scalar variable "@locations".

DECLARE @locations varchar(50), @email varchar(100)
declare t_loc
cursor for select locations,storeemail from #ezpayloc
open t_loc

fetch next from t_loc
into @locations, @email

when i run the fetch statement im getting the error

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-19 : 16:45:55

You didn't post the entire code, so it's hard to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-19 : 16:50:11

I am confused all i need to do is take the results of this

'SELECT [LOCATION],[CUSTOMER],[ACCOUNT],[CUSTOMER_NAME],[PHONE]
FROM vw_PAST_DUE_LESSONS_EZPAY]'

and email it to diffrent locations that come up in that query

the emails for the locations are saved in a #temp table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources