> A stored procedures that returns 500,000 will build a 500,000 output set in memory FIRST, THEN it will start returning the first
> row.

No, it does not. Selectable stored procedure itself returns each row immediately. But

* SELECT command sends several rows at once to get better performance over network
(depends on network configuration and row size, can be e.g. 20 rows, never anything like 500000)
You can turn it off by FOR UPDATE clause.

* If you want e.g. to sort the result, than of course there is no other way than to fetch everyting first.

> A stored procedure call in Firebird is atomic.

Actually not. Selectable stored procedure is atomic only between SUSPEND calls,
i.e. if an error occurs, the work is undone only to the point the last SUSPEND was called.

Ivan
http://www.volny.cz/iprenosil/interbase/