Hi Jason,

> I will give you a perfect example of wanting to do a Commit
> within a stored procedure. Currently, I have a messload of
> stored procedures that work like function calls, in that,
> they do a bunch of wacky stuff, INCLUDING insert values into
> a table, and if it succeeds, it returns the new records
> unique ID in a single field called "result", and "result" is
> null if something went wrong. The problem is, being that it
> is fired in a select statement, the inserts just dangle.
> Fortunately, the Easysoft ODBC driver has an explicit option
> to "Commit on Select", but if this didn't exist, without a
> commit within a stored procedure, this wouldnt work out too
> well.

This option in the driver is not doing anything you could
not have done explicitly yourself. The commit is still
occuring outside the stored procedure, transaction control
still rests with the client.

I am not sure what you mean about the "inserts just dangle",
it seems likely that you are referring to the fact that the
client representation of the transaction is not aware that
your select statement is making changes to the database (it
is assuming that a select is non-modifying). This is quite
different to the problem of commits in SPs - indeed the fact
that you think commits in SPs could solve this particular
problem shows just how dangerous it could be.

--
Geoff Worboys
Telesis Computing