Dear All,

I've recently wrote a stored procedure that took sometime to complete,
since it's processing (deleting and inserting) quite a lot of data. On
average, it will process around 10,000 records per run, and took around
7-10 minutes to complete.

The structure of the stored proc is something like this :
...
create procedure sp_process_all( pk bigint )
returns (
status integer,
count_processed integer,
count_failed integer
) as begin
for select (blah, blah, blah)
do begin
execute procedure sp_process_one_detail( detail_pk );
count_processed = count_processed + 1;
when any do begin
status = -1;
count_failed = count_failed + 1;
end
end
end
...
create procedure sp_process_one_detail( pk bigint )
as begin
//get data,
//process - calc
//post data
end
...

The thing works ok, i suppose. The problem is in the user interface. I
have a button on screen (Delphi) that will execute the sp and shows the
result (how many succeeds/failed, etc) afterwards, but pushing this
button means the user is going to have to stare at the hourglass cursor
for minutes without any visual cues.

Can anyone offer a better suggestion on how to improve this?

One approach i'm thinking about is to get rid of the outer stored
procedure, and call the inner stored procedure from the client side....,
something like the following (in delphi-pseudocode):

...
var
query : tdataset;
i, max : integer;
...
//get all pk to be processed...
query.close;
query.sql.text := 'select pk from blah blah ....';
query.open;

//get recordcount
...

//loop through all the pk
while query.eof = false do begin
//process one record
execute procedure sp_process_one_detail(query['pk']);
query.next;

//show progress bar here...,
end;
...

So basically i'm rewriting the outer sp in delphi code, but this smells
'funny' to me :D. Any ideas will be appreciated.

Thank you very much in advance.
sugi.