T-SQL Loop on selected query

[Cshrek]

hi,

i have a small query :

-----------------------------------------------------------------------

SELECT *
FROM Fares INNER JOIN Routes
ON Fares.RouteID = Routes.RouteID
WHERE Routes.Origion = @Origion
AND Routes.Destenaition = @Destenaition
AND Fares.ValidFrom < @Date AND Fares.ValidUntill > @Date

-------------------------------------------------------------------------

as i see it my first option is get the results as the are back to my code and do all the loops in there while executing another Query's, but that will consume lots of network traffic between my web application and the SQL Server.

so am wondering if there is any way to loop on each row that was returned from the query above


thank you.

[Delerna]

huh ???
what loops???
what other query ???
You don't have enough info

[Cshrek]

sorry for the miss info.

i mean, after the above was executed, i like to loop on each row that the query returns, and execute another SELECT on the table that returned.

but am not so sure if it is possible, so excuse me if not please.

[Delerna]

I am pretty sure you can but
more detail on the first query in particular field names??
is the first query in a stored procedure or where??
more detail on the second query??
what extra info are retrieving when you run the second query on the first

perhaps a diagram of the fields and data from the first query
and a diagram of the data that will be returned by the second query

[Cshrek]

Oky thanks a lot for the willing to help.

as i already found some solution for the current problem, am still face times when i like to loop on returned tables. so i will give an example:

----------------------------------------------

SELECT * FROM Customers

will return:

Name LastName Age

Jacob Smith 35
John Doe 63
Anja Brow 43

----------------------------------------------

so now for example, i would like to get the total age of all the customers.
and so i can do SELECT SUM(Age). but in case where it will be more complicated then just the sum, how will i iterate trough each row and use the Age value for something.
any example of just how to loop to each row and do something on it such as getting the name and using it for nested select or something would be grate.


hope my question make sense. thank you very much.

[Delerna]

The simplest and possibly the easiest way to iterate thhrough a recordset is to use cursors (check your SQL helpfile for specifics and if you have problems ask how to resolve the specific problem). Cursors however can be slow, and the more data you have in your recordset the slower they become. Therefore cursors are only recommended when you have small amounts of data and you can't come up with a set base solution.

Set based solutions are when you work with the recordset as a whole rather than stepping through it 1 record at a time. Set based solutions are much faster than cursors.
As far as examples are concerned, there are a million and 1 possible ways of achieving a result and the particular solution you come up with will depend on the data you have and the results you want to achieve. I probably exagerate with 1 million and one, but youve heard the expression "theres more than 1 way to skin a cat"
Give me half an hour and I will try and post something here that will get you started.

[Delerna]

Sorry couldn't think up a simple scenario that made sense to do in a loop so I will just do an example without worrying about making sense

we have this table called tblStock with the fields
Sun,Mon,Tue,Wed,Thu,Fri

and on sunday there is a certain amount of stock recorded. At the end of each day the amount that has been added and removed is recorded in another table called tblTodaysMovement. After the movements have been recorded the exampl loop is run to update the stock

Declare @Day int,@Added int,@Removed int, @Stock int
set @Day=1
while @Day<5
begin
   set @Added=(Select Added from tblTodaysMovement)
   set @Removed=(Select Removed from tblTodaysMovement)
   set @Stock=(Select case when @Day=1 then Sun else 0 end + 
                                 case when @Day=2 then Mon else 0 end +
                                 case when @Day=3 then Tue else 0 end +
                                 case when @Day=4 then Wed else 0 end +
                                 case when @Day=5 then Thu else 0 end 
 
   if @Day=1 begin update tblstock set Mon=@Stock+@Added-@Removed end
   if @Day=2 begin update tblstock set Tue=@Stock+@Added-@Removed end
   if @Day=3 begin update tblstock set Wed=@Stock+@Added-@Removed end
   if @Day=4 begin update tblstock set Thu=@Stock+@Added-@Removed end
   if @Day=5 begin update tblstock set Fri=@Stock+@Added-@Removed end
   set @Date=@Date+1
end
 

As I said, this is not a good example scenario and there is no way I would do it like this. It is just an example of one way of looping a query and adjusting it according to parameters that change on each loop. I have better examples but they are too long and complex to explain here.

I hope you find this helpful in getting you started

[Cshrek]

oh, yeah it seems like very good answer.
thanks a lot (: