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