At 02:25 PM 25/09/2003 +0000, you wrote:

>Hi Svein,
>
>You are right !!!
>This problem occurs because of "CommitAction = caRefresh" setting.
>I changed with "CommitAction = caClose" and the problem disapeared.

Yes: because a "selectable stored procedure" is NOT a table - so
"refreshing" it means executing it again!!

>I think these kind of problems can be solved breaking down the SP in
>2: one for select and another one for update/insert.

Exactly. The database will not prevent you from executing DML inside a SP
that outputs a multi-row set -- but that is not a good reason to choose to
do so. "Render unto Caesar that which is Caesar's". There are other good
reasons not to execute DML inside a selectable set.
1. Most importantly, when you write that sort of SP, your user is looking
at generated data inside the same transaction that has pending uncommitted
DML. Should an exception condition arise during the time when the worked
was posted and the transaction commits, there could be great confusion for
the user as to whether her work was done at all.

2. Another way to cause unwanted re-execution in this style of SP is to
read the dataset's RecordCount property! It will do select count (*) from
TheProc(param..) and run the executable code all over again. This is
currently the source of a serious bug in IBOConsole's query utility.

Helen