How can i use a for loop in a query ?

hi all i have the following stored procedure.

declare @.AreaID int

select @.AreaID = 1

select DATEPART(hh, dbo.JobEvent.JobEventDateTime) AS hourbracket, count(ID) as NUMCOUNT

from
table1 INNER JOIN
table2 ON table1.JobId = table2.JobEvent_JobId
where

(table1.Job_AreaId = @.AREAID)

the there are about 300 AREAID's so my question is do i do a "LOOP" or is that no efficient and chews up resources on the server ?
what would be a nice clean way to do this ?

thanks
robby

Are you asking how to iterate over the results returned from your stored procedure?

depending on how you retrieve you results either from a data reader or you used a data adapter and filled it to a dataset

//if filled to a dataset you can do thisif (MyDataSet.Tables.Count != 0){foreach (DataRow rowin MyDataSet.Tables[0].Rows) {//now you can access each row returned }}//if using a data readerwhile (DataReader.Read()){//hear you can access each row }

|||

Hi,

if i lets say hardcode the areaid to lets say 1 i get a set of results. i want to next get the results for areaid 2 so as if i want to runt the stored procedure again. But can i lets say have some sort of loop which will pass the parameter (areaid ) into the stored procedure and get the results in c# ?

that would be i think a bit too long winded ... is that right ?

|||

Sorry, but I do not understand what you want.

|||

What I understood from the problem is that you want to execute the same query for around 300 AREAID.
If I am correct following query will solve your problem:

SELECT table1.Job_AreaId, DATEPART(hh, dbo.JobEvent.JobEventDateTime) AS hourbracket, count(ID) as NUMCOUNT
from table1 INNER JOIN table2 ON table1.JobId = table2.JobEvent_JobId
where table1.Job_AreaId in
(
SELECT the list of distinct AreaId for ex: select AreaId from MasterTable
)
Group by table1.Job_AreaId

Hope this helps you!!!

|||

Hi ,

Thanks for the response . It sure did help much appreciated

robby