Topic awaiting preservation: Stored Procedures (Page 1 of 1)

I have a table in a SQL database with the fields

date
building_id
room_id
quantity

whhat I need to do, is write a stored procedure which will update the quanitiy given a start date/end date, and if no record exists for that date, create a new one.

ie: if Ihad a sotred procedure UpdateDates '07/13/2003', '07/20/20003', 3, 'QN', 5

I'd want to either update an existing date. (for what I need, this would add 5 to the value of quantity), and if no date exisits, create a new one.

Prior, I was using an ASP script and checking each date individually... but due to the nature of the application, ended up banging off upwards of 900 queries per request which is well, a tad much.

I know I could probabably do an

update tablename set value=value+5 between X Y (along that lines) .. but for that to work, even for the dates which currently dont have a record, thus not in the table, nothing would be done.

any ideas?

well, I don't know much about stored procdures, but they're no silver bullet...
I don't think they'll improve your problem much.

now, why don't you "select date,buliding_id, room_id,quantity from myTable order by date:",
push the results into a array (don't ask me about the asp code for that, though),
increase the quantitiy,
and do one massive,
"insert into myTable2 () values ([...]),([...]);"
"drop table myTable"
"alter table rename table mytable2 to mytable" (or so... I'd have to look this one up).

or alternative 2:
-step one: Select date from myTabre where date >= X and date <= Y
-insert all the dates that are not in there already, again one big insert statement (or serveral chunked ones)
-do one update quantity where date >=x and date <= y

Wouldn't that help?
If it doesn't, I'd wager even money that you could setup your table in a much more efficent way for whatever it really is you're trying to accomplish.

despite there being plenty of good systems out there to buy, I am stuck trying to program a basic hotel reservation system.

The admin, logs onto the website, and lets say selects a start date/end date from a calander control, from a drop down selects a building (there are 3 different buildings for this company), from the next drop down, he selects room type

in the field next to it, he either enters +# or -# [add X rooms to those dates, or remove N rooms from those dates]

So, a select * from room_inventory looks approx like

07/12/2003

What I think I'll do is a mix of ASP/Stored Procedure actually. have an ASP Function that loops though the start/end dates, and fires off a request for said date.

how its going to be done actually (just had a quick msn meeting thing), streamlining down how the user interfaces with it.

I still think your table's messed up.

Here's how I would set it up

Buildings Table
building_id
name
...

rooms table
room_id
description
...


bookings table
room_id
date

now, if you wanted to book say, room type AC, from july first till july 7th,
You'd do a 'instert into bookings (room_id,date) values (AC,07-01-2003),(AC,07-02-2003)...

want to know how many are booked on day x?
select count(room_id) from bookings where date = X
same for date range.
Deleting is a tad more difficult, you first have to get a count(), then delete the rows (via where
room_id=x and date=y), then add back in as many as there are now.

Additionaly, you'd probably want to throw away old records somewhere along the way.

With a double index on room_id and bookings, this would probably be pretty fast, too.

so long,

Tyberius Prime