table variables in stored procs

[astromenix]

hello,
I m in the situation that I got to calculate room prices for given periods and depending on their occupancy generate different prices and then apply discounts on each day.

So I need at some point, when i get data of different tables to loop through each date from check_in and check_out dates. I m wondering, whether is wise to use a select to limit the records based on the check_in and check_out dates and then store them in a table variable. Then instead of looping and select price of the original tables, use the table variable(s).

I m concerned of whether that will be costly in terms of memory (as table variables if got not too many records stored entirely in memory) and database performance to keep my data in table variables.
I was also thinking to store in different variables the prices of different types of discounts and meal prices as they also are dependent to periods.

I would appreciate any advice and thoughts
Thanx a lot

Astromenix

 

[astromenix]

Ii’d like to rephrase my question
I must return a similar result as the expedia’s searc for hotels.

Is it a good idea to select all the rooms and their prices by filtering them by dates and other criterias and them results to store em in table variables and then while I loop for each room and dates of the request select the table variables rather than the original tables?
Or should I just select the data from the different tables perfoming joins for every room?
Thanx

 

[Pattycake245]

For what you are trying to do, I don't think a loop is necessary. You should be able to select all applicable records from the table(s) and do the room calculations all in one query. As far as answering your question, there comes a point where table variables aren't the best thing to use. I have found that table variables are only good to store fairly small record sets whereas temp tables allow for a greater number of records to be stored. If the case arises where you need to pull data and manipulate it after the fact and the number of records is small, then a table variable is the way to go. But for what you need I think just pulling the data from the original tables is all you need.

Tim

 

[astromenix]

hello everyone and thanx Pattycake245 for yr answear(really apreciate this).
the things ia that i can perform the calculation using only a select .
for example a search request may have one to four rooms, each room may have one to five children and their ages.Now for each room and each childen of the room need to be checked different conditions that are stored in different tables, as children discounts and their own age groups, meal discounts, meal prices, room different rates (a 3 person capacity person room--may sold in different prices for 1, 2 or 3 persons).
According to certain criteria, i need to choose and show on the client the specific "rate", accordinf to children in rooms apply the discounts on prices per day, according to number of nites aply extented stay discounts etc..
Now based on the client's request (ex.location, class) i have limited the number of hotels that i will show. next by using them hotel id i choose their room s and their prices For the requested period and store them in a table variable...one of them rooms may fit two requested rooms...so for each room i can use the same table variable to search instead my physical tables(5 tables to get the data).
Also discounts are based on the period....so i got another table variable to store the discounts, as will apply for every room under ceratin conditions.


its is estimate that the discounts table variable may have zero to 10 records.

So it is wise to perform selects while i check for conditions or store em in some table variables and then query them for gettin data..
soz for the lenght of my post....just tryin to explain the sitution...
thanx in advance