Will,

>    Is it possible to select a list of read only stored procedures from the
> database.
>
>   I'd like to give my reporting engine access to stored procedures where they
> fit the following rules
>
> 1.) Must be selectable
> 2.) Must have no input parameters.
> 3.) Must have one or more output parameters.
> 4.) Must not perform any database writes either directly or indirectly (via
> another procedure call.)
>
> I have 1-3 sorted with the following SQL but I can't see if 4 is possible. Can
> anyone point me in the right direction please?

While I don't know if #4 is possible, I would suggest that even if it was ... depending on your parsing of the system tables to make such a determination is a very "brittle" approach.

If a future Firebird version changes the structure/definition of the system tables, you would be "locked in".

Instead, I would recommend that you adopt a naming convention (a specific prefix) for the SPs which would want to expose to your reporting engine. A prefix like "Report_", "Report$", "RP_" or "RP$" would be my suggestion.

That approach has the advantage that you can directly control which SPs are exposed -- there could be other SPs which meet the criteria that you wouldn't want exposed.


Sean