At 04:46 PM 18/02/2004 +0000, you wrote:

>Helen,
>can you explain the part about the transaction not being commited.
>I am not sure I understand why the commit in the original code was
>not doing anything. Was it becasue the suspend was in the proc

It would if there was more DML after the suspend - suspend just behaves
like exit in an executable proc.

> or because of the storedprochasDML property setting(which I for sure
>did not have set)?

Yes, but it isn't that straightforward.

It is still a fundamental problem to have a double-use SP. Even though you
*can* perform DML in a selectable SP, you shouldn't, because the DML won't
get committed until you commit the whole transaction. When you return the
dataset to the SSP, it is still in uncommitted state. You can't commit the
SP because you need the output rows.

If you don't need a dataset, the proper way is to use a data access object
to post the DML directly and return a set of output parameters - in your
case, just a single ID value, as I demonstrated.

If you actually want to have a multi-row dataset, and post DML *from* it,
it is a different construction -- not as you tried to do it. In that case
you use the xxxxxSQL properties of the dataset to execute some custom DML
(simulating the behaviour of a real dataset). That custom DML can be
insert, update or delete statements (respectively); or any of the xxxxSQL
can EXECUTE its own special executable SP.

If you need to *display* the work and perform DML as well, then that's
the way you'd need to do it. It's different from a real dataset because, in
order to "refresh" the set after committing, you have to close the dataset
and invoke the SSP anew.

Helen