By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,905 Members | 1,574 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,905 IT Pros & Developers. It's quick & easy.

T-SQL Loop on selected query

Cshrek
P: 8
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.
Jan 18 '08 #1
Share this Question
Share on Google+
7 Replies


Delerna
Expert 100+
P: 1,134
huh ???
what loops???
what other query ???
You don't have enough info
Jan 18 '08 #2

Cshrek
P: 8
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.
Jan 18 '08 #3

Delerna
Expert 100+
P: 1,134
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
Jan 18 '08 #4

Cshrek
P: 8
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.
Jan 18 '08 #5

Delerna
Expert 100+
P: 1,134
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.
Jan 18 '08 #6

Delerna
Expert 100+
P: 1,134
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

Expand|Select|Wrap|Line Numbers
  1. Declare @Day int,@Added int,@Removed int, @Stock int
  2. set @Day=1
  3. while @Day<5
  4. begin
  5.    set @Added=(Select Added from tblTodaysMovement)
  6.    set @Removed=(Select Removed from tblTodaysMovement)
  7.    set @Stock=(Select case when @Day=1 then Sun else 0 end + 
  8.                                  case when @Day=2 then Mon else 0 end +
  9.                                  case when @Day=3 then Tue else 0 end +
  10.                                  case when @Day=4 then Wed else 0 end +
  11.                                  case when @Day=5 then Thu else 0 end 
  12.  
  13.    if @Day=1 begin update tblstock set Mon=@Stock+@Added-@Removed end
  14.    if @Day=2 begin update tblstock set Tue=@Stock+@Added-@Removed end
  15.    if @Day=3 begin update tblstock set Wed=@Stock+@Added-@Removed end
  16.    if @Day=4 begin update tblstock set Thu=@Stock+@Added-@Removed end
  17.    if @Day=5 begin update tblstock set Fri=@Stock+@Added-@Removed end
  18.    set @Date=@Date+1
  19. end
  20.  
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
Jan 18 '08 #7

Cshrek
P: 8
oh, yeah it seems like very good answer.
thanks a lot (:
Jan 18 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.