For each Row in a query execute another query
-
Hi
i want to write a stored procedure which executes for each row in a query:
select email from ST$Istek I
inner join ST$Kullanicilar K on I.Istek_Sorumlusu=K.id
where istek_durumu<20
group by email
then it executes another query
begin
select istekid,istek1+istek2 as Istek from ST$Istek I
inner join ST$Kullanicilar K on I.Istek_Sorumlusu=K.id
where istek_durumu<20 and email=@email
// Also i dont know how can i get email info from the first query
EXEC msdb.dbo.sp_send_dbmail
// also the recipient in this mail will be @email from the first query
end
How could i complete it ? how can i write it? Could anyone can help me on that query?
-
You can join the queries or make a subquery.
select istekid,istek1+istek2 as Istek
from ST$Istek I
inner join ST$Kullanicilar K on I.Istek_Sorumlusu=K.id
where istek_durumu<20 and email = (
and istek_durumu<20
I think that is what you want, but you'd have to work with it to see if it's the correct data.
As for sending the emails, you would likely need a cursor to run through these and send the emails from the query. Look up cursor in Books Online or this site for information to do that.
-
i think i couldnt understand my problem clearly
First of all there are employees customers and the requests of these customers. Employees are assigned to the customer's requests. and i want to remind them their not responded requests as mail.
now to do that i think in the first step i should select each employees email address who have not responded request
then put it in a loop (for while or for each)
begin with a query which returns the first row employee's not responded requests
then send email it in an excel or table format to that employee
finish the loop (turn to the begining since the first query's rows end)
I wnat to prepare the request query in Reporting services to looks nice
If this problem is possible i have 3 questions now.
1- how could i take the email adres of the employee to assign it in where clause of the query in loop
2- i dont know how could i use for each loop could you give me an example
3- Is Sending this query in table in Reporting Services is possible? (i know how to prepare a report in a table in Reporting Service but i dont know to execute that query for every parameter and send mail to that SPECIFIC person)
-
i learned how to use cursors and improve my code as:
DECLARE cr_Email CURSOR FOR
select distinct K.email from ST$Istek I
Left Join [dbo].[ST$Kullanicilar] B ON I.istek_sahibi=B.id
left join ST$Kullanicilar K on I.Istek_Sorumlusu= K.id
left join ST$IstekSatirlari IST on I.istekid=IST.IstekNo
where istek_durumu 1 and K.email is not null
Declare @email varchar(70)
OPEN cr_Email
FETCH next from cr_Email into @email
WHILE @@FETCH_STATUS =0
begin
select distinct istekid,istek1+istek2 as Istek,B.isim as isteksahibi
from ST$Istek I
Left Join [dbo].[ST$Kullanicilar] B ON I.istek_sahibi=B.id
left join ST$Kullanicilar K on I.Istek_Sorumlusu= K.id
left join ST$IstekSatirlari IST on I.istekid=IST.IstekNo
where istek_durumu 1 and K.email=@email
FETCH next from cr_Email into @email
end
Close cr_Email
DEALLOCATE cr_Email
but again i have problems.
this stored procedure executes for only the first row of the first query. I mean in the first select query it returns 3 email addresses but when i execute it in Reporting services
it sends mail of the email's information. It should be executed all of the rows.
You can see the result of executed stored procedure in sql server management studio
So as you can see this stored procedure has 3 result set. it must be execute for every result set and send 3 mail.
how could i do this in reporting services? also how can i arrange the email subscriptions ?
in the first execution the first result set will be sent to the first email address second set will be sent to second address...
how could i done it?
Please help i am so close to the end.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply