Tim,

T> DECLARE PROCEDURE MYPROCEDURE
T> (
T> INPUT VARIABLES
T> )
T> AS
T> BEGIN
T> BEGIN TRANSACTION
T> INSERT INTO THISTABLE
T> EXECUTE THATPROCEDURE
T> EXECUTE ANOTHERPROCEDURE
T> DO SOMETHINGELSE
T> COMMIT TRANSACTION

T> EXCEPT
T> BEGIN
T> ROLLBACK
T> INSERT INTO ERRORLOGTABLE
T> (X, Y, Z)
T> END
T> END

T> So it allows you (in the above example, anyway) to do an audit trail of
T> what is happening. Error logging is the thing I have seen it used for most
T> often, but I have also seen other examples. Basically, as soon as you do
T> more than just one thing in a procedure (i.e., as you would in code),
T> having transactions within stored procedures becomes useful.

Is there any practical advantage in not doing it client side?

T> The one place I have seen it mis - used is where a developer will use it to
T> do something that should have been done in client - side code - for
T> example, where two things happen which should both be in the same
T> transaction context. Because the developer has included the transaction
T> context within two separate stored procedures, it means that they have
T> separate transaction contexts, and so the use of transactions is meaningless.

I have almost always seen transaction control in stored procedures
used as a means to overcome the inability of the server to
automatically undo the work done by a failing stored procedure. I may
well have seen almost always bad examples. ;-)

Ciao
--
Nando mailto:nandod@...