Subject Re: [firebird-support] return multiple tables in a dataset
Author Paul Vinkenoog
Dixon wrote:

> Is it possible to write a procedure that will return multiple result sets in a dataset? If so whats it look like.
> here is sample of what I've been trying, but its not working correctly. Instead of getting two separate tables, I get all the
> results for field1 and field2 in the same table.

That's as designed. A selectable SP just spits out records. Together they form a single dataset, regardless where the original data came from.

If you want to signal transitions within the return set (e.g. to indicate that the rows to follow come from a different table), you can add an extra output column, e.g. an integer column starting at 0 which is incremented at avery table change. The code that calls the SP can then watch this column and take some action when it changes (for instance, insert some empty lines in the visual output, or whatever).

In general however, it's better to use multiple calls if you want multiple return sets - either to different SPs, or to an SP with input parameters. That's "cleaner" and more natural than resorting to kludges like the one described above.

Paul Vinkenoog