[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