SPL example. Comments?

Neil wrote: >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? Hmmm, I cannot understand why you might find it easier to do what you have described instead of using the 4GL/SPL interface to accomplish the task. I can only assume that the lack of documentation is the real show stopper. From what you have given, all you need in the 4GL/SPL is: 4GL ------ PREPARE Astmt FROM "execute procedure Fred(?)" DECLARE Acurs CURSOR FOR Astmt OPEN Acurs USING p_group_code { Get the value in } FOREACH Acurs INTO ... .... END FOREACH SPL ------ FOREACH SELECT ..... INTO .... FROM ... WHERE ... ..... RETURN x, y, z WITH RESUME END FOREACH Hope this helps Mark Denham BBC London, UK Mark.Denham@bbc.co.uk