For each Row in query exec another query

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

SQL Server 2005 Forums

Transact-SQL (2005)

For each Row in query exec another query

Author

Topic

elephantman
Starting Member

6 Posts

Posted - 2008-10-18 : 04:54:52

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 07:28:30

[code]select {I/K}.email from ST$Istek I
inner join ST$Kullanicilar K on I.Istek_Sorumlusu=K.id
cross apply(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={I/K}.email
)
where istek_durumu<20
group by {I/K}.email[/code]

i dont know which table contains email column. so use appropriate alias (I or K) on above code. for senting email to each of email declare a temporary table

[code]CREATE TABLE #Temp
(ID int identity(1,1),
email varchar(1000)
)
INSERT INTO #Temp (email)
Above query here[/code]
then use a loop like below

[code]DECLARE @ID int,@Email varchar(1000)

SELECT @ID=MIN(ID)
FROM #Temp
WHILE @ID IS NOT NULL
BEGIN
SELECT @Email=email
FROM #Temp
WHERE ID=@ID

EXEC msdb.dbo.sp_send_dbmail otherparams,@Email...

SELECT @ID=MIN(ID)
FROM #Temp
WHERE ID>@ID
END[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources