Stored Procedure Strategy - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure Strategy

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2006-11-30 : 13:51:33

Hello all,

I have a problem that I would really appreciate some expert advice on.

I am designing a web application for a chauffeur company to accept on-line bookings. I have a table called 'Bookings_Details' that holds each passengers name, pickup location, dropoff location, order of pickup and bookingID which relates back to the main 'Bookings' table. My problem is this:

I need to implement a facility that will allow the booking owner to edit the details of the booking. This will include removing and adding passengers. Each booking can have between 1 and 6 passengers. This is straightforward until I get to the pickup order of the passengers.

If, for example, I have a booking with 5 passengers and 2 are to be removed, lets say they are the the passengers with pickup orders 2 and 4. This would mean that in my Stored Procedure I would need to remove the two passengers and then write in the procedure that Passenger3 becomes Passenger2 and Passenger5 becomes Passenger3.

I'm sure you can see how complex this can become when you have to allow for all variations of passenger removal. The only way I can think of doing this is with lots and lots of IF's but this would be a nightmare to write, making sure every possibilty is covered and would probably perform badly. Can anyone offer me any better advice please.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-30 : 14:30:54

In the table the pickup numbers just have to be in the correct order right, so how about making them say 1000 and 2000 when you first insert, then if you add a third and it is between 1 and 2, you make it 1s number plus 1, so now you have 1000, 1001, and 2000. When you query them you just sort by that number and they come out in the correct order. You could use a linked list approach or update all the rows each time you make a change, but this would be much simpler.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-11-30 : 14:53:04

[code]
declare @BookingDetails table(Name varchar(50), PickupOrder int)

INSERT INTO @BookingDetails
SELECT 'Dustin', 1 UNION ALL
SELECT 'Brandon', 2 UNION ALL
SELECT 'Sam', 3 UNION ALL
SELECT 'Edie', 4 UNION ALL
SELECT 'Jane', 5 UNION ALL
SELECT 'Sally', 6

SELECT * FROM @BookingDetails

DELETE FROM @BookingDetails
WHERE Name IN ('Dustin', 'Jane')

SELECT * FROM @BookingDetails

UPDATE a
SET PickupOrder = (SELECT COUNT(*) + 1 FROM @BookingDetails b WHERE a.PickupOrder > b.PickupOrder)
FROM @BookingDetails a

SELECT * FROM @BookingDetails

[/code]

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 15:12:26

I would have a Stored Procedure for Add Pickup which takes an Order Sequence value. If a matching Order Sequence value already exist (for that Booking) I would increment [by one] all existing Pickups that that Booking which are >= the Order Sequence value - thus a new Pickup could be inserted with the sequence value of the pickup that it should be put in front of.

Deletions don't matter provided that the Pickup List is displayed in order. But you could close-up to prevent gaps if its important for some reason, but I would try to avoid that Cleanup-Step.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources