SPL Example. Comments?
We did some work on stored procedures the other day but without a wealth
of documentation, all atempts are a shot in the dark. Here's a trick
we used and I wonder if the learned members of this community could comment
on whether there is a better way, or whether we really are quite clever
(doubtful!)
We wished to have the database return to us a set of names along with
any money they owed us. The first thing we looked at was a foreach using
return with resume, but we found it difficult to work with.
In the end we opted for the creation of a temp table which either
the
4GL program can use, or if you are debugging, viewed from SQL. This was
very satisfactory and allowed us to change the rules without altering our
programs one bit. One trick we tried was the following :
begin
on exception in(-206)
end exception;
drop table t_temp; end
The procedure could then be run as many times as you liked. However...
One bug we did find (if it is a bug), and I would like this explained was
the set was returning more than we wanted for our 4GL, so we had a delete
statement right after the execute procedure clearing out rows we didn't
want .i.e.
execute procedure sp_total(p_group_code)
delete from t_sp_total
where client_code != p_client_code
First time round, all OK. Second time round, the delete statement doesn't
know what temp table you are talking about! Is a pointer allocated to the
temp table in the 4GL which it uses for future reference and when the
stored procedure executes a second time in effect we have a new temp table even
though I am referencing it by the same name. In other words, does the
4GL not use the temp table's name, but rather some other reference?
Just curious.. cheers!
--
----------------------------------------------------------------------------
Neil @ Storm. Any ideas expressed above are the feelings of
my employer. I own the company.
----------------------------------------------------------------------------