[thelist] MSSQL, transactions, stored procedures and multiple inserts (phew !)
Paul Cowan
evolt at funkwit.com
Thu Jul 18 18:22:00 CDT 2002
Steve Cook wrote:
> 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?
Nope. There's no way of passing arrays to SQL Server, much to my chagrin.
So the "split string" idea is pretty much it, if that's the way you
want to do it.
So, anyway. You've got an existing stored proc (say, proc_MakeBooking)
which has BEGIN TRAN/COMMIT/ROLLBACK whatever in it. You need to call it
multiple times, with the option of a "global rollback".
Option 1) (what you allude to above) Create a master stored proc,
proc_MakeAnAwfulLotOfBookings(@Bookings varchar(x)), which
does (in pseudocode) something like
BEGIN TRAN
create table #sometemporarytable(whatever...)
split (@Bookings) into its component parts, dump 'em into
#sometemporarytable
declare a cursor on #sometemporarytable
loop through the cursor, calling proc_MakeBooking once for each row
drop table #sometemporarytable(whatever...)
COMMIT/ROLLBACK
If your question is fundamentally "can I have
BEGIN TRAN
BEGIN TRAN
COMMIT
COMMIT
", where the inner 2 and the outer 2 are in separate procs, then yes, you
can: sort of. But be careful, because there's a trick: COMMIT commits the
innermost transaction: but ROLLBACK rolls back *all* open transactions.
Singularly unhelpful, I know: but there are some things I happily admit
I just don't get about SQL Server, and that's one.
Remember, if proc_MakeAnAwfulLotOfBookings has a TRAN, then there's
theoretically no need for proc_MakeBooking to have its own, unless it's
called independently elsewhere.
Option 2) You don't say what coding environment you're using: if you're
using
ASP with ADO, you can actually manage your "outer transactions" in ADO.
The Connection object has transaction-handling methods. So in ASP, you can
do:
cnnMyConnection.BeginTrans
for each booking in whatever
' call proc_MakeBooking
next
cnnMyConnection.CommitTrans ' or whatever
Personally, I would be inclined to hook the transaction out of
proc_MakeBooking. If there's an error, return some status code: and
handle that in proc_MakeLotsOfBookings, or in ADO, with a 'high-level'
transaction.
Hope this helps: transactions are very powerful, but also alarmingly
complicated! Good luck.
Cheers,
Paul
More information about the thelist
mailing list