>5. They are a PITA when doing multi table transactions. For example try to wrap an order and multiple orderlines into one transaction.
>
>I have to admit, I honestly do not understand why you think that can't be done, or why you believe they can't be done. Between transaction objects, error handling capabilites in SQL 05, etc....this is hardly a PITA. If you really and truly feel this is an issue, I'll post a small sample to demonstrate this.

I guess you're refering to calling multiple SPs from the client and handle the transaction on the client level. I was referring to having the whole transaction in the SP itself, in which case you have to pass the data of the whole transaction to the SP, which is a PITA. In your case it is very similar to what you'd do with dynamic SQL, though with dynamic SQL you'd could generate a script and execute it as a transactioned script and execute it in one single roundtrip.