[thelist] MSSQL, transactions, stored procedures and multiple inserts (phew !)

Steve Cook steve.cook at evitbe.com
Thu Jul 18 10:30:01 CDT 2002


Hi database gurus!

I'm building a site that is expecting fairly high traffic loads to handle
booking enquiries for a large event. My data structure includes:
	A table to hold personal details (tblPerson)
	A table for details of a single booking (tblBooking) which includes
a booking ID a link to the person who placed the booking and some other
generic info
	A table which holds information about the various types of ticket
available (tblItem),
	Plus a relation table (tblRelItemBook) which links the type of
ticket (itemId) to the booking information (bookId), plus the number of
tickets booked.

I'm using a stored procedure to insert data to reduce the load between the
webserver and the DB server. I'm also wrapping the SQL in my stored
procedure within a transaction so I can roll back the whole shebang if I get
a problem during the updating.

My problem is with the last part of the list above - I need to perform
multiple inserts against the relation table for the different tickets booked
(there are well over 20 different ticket types which can be booked in all
sorts of different quantities). At the moment I have no idea of how I can
get this list into the database within the same transaction. What I'm
looking for is something that does the eqivalent of looping through the
list, performing an INSERT on each. However right now I don't even know how
to get the information into my stored procedure - should I import it as a
VARCHAR and then split it within the SP, or can I import it as an array in
some way?

As you can probably tell I'm pretty confused - any general ideas or am I
barking up the wrong tree by trying to encapsulate everything in a single
stored procedure? If so, how do I ensure transactional integrity over
multiple database operations?

*phew* Sorry for the long question. Any help appreciated on this one!

.steve


-------------------------------------
 Cookstour - http://www.cookstour.org
-------------------------------------



More information about the thelist mailing list