Subject Procedural vs. declarative (Re: Select procedures)
Author Sergio Govoni
I do understand viewing select procedures as parametric views; but sometimes
you really need to see what is happening "inside the box".

1) I found that complex inserts with subqueries that take a lot of time can
be rewritten as an "inserter" procedure with for select do.. insert and this
improves performance; adding a suspend statement at the end lets you see the
job row by row while it is being done, if you are in isql.
Moreover, if you were to insert thousands of rows from a 'dirty' source and
one of them would break referential integrity, thus cancelling the whole
insertion, at least this way you could see the offending row; with a plain
insert you can not.

2) If you wanted to debug an executable procedure, you could simply add a
return parameter and a suspend statement and see the results.

3) And what if you have to collect data from different tables, format them
and then give them back to the caller? how easily could it be done with a
'create view', which is declarative by nature, not procedural?

The point is this: you'll need some form of procedural language; and under
two-layer client/server architecture it is better to have it inside
Interbase rather than in a host language, which would force you to add an
extra software layer.
Isql then saves you when you switch operating systems, since you copy the
.gbk file and everything is there, including procedures; no recompilation

Since currently there is no JVM within Interbase, today's select procedures
can be a decent solution to a number of problems.

Sergio Govoni