Using ClassQueries() as Tables
Similar to Caché so also in IRIS, you have ClassQueries available in 2 variants
- Basic class queries, which use the class %SQLQuery and an SQL SELECT statement.
- Custom class queries, which use the class %Query and custom logic to execute, fetch, and close the query.
The related documentation is excellent and very detailed.
You will see a bunch of examples of how to use it with %ResultSet or %SQL.Statement
Following the suggestions, you will add the keyword [SqlProc] to make it visible in SMP or over JDBC/ODBC
Attention: [SqlProc] Doesn't distinguish whether your procedure returns a scalar value (acting as StoredFunction)
or if it returns a ResultSet. You have to check the definition to see the difference.
Returning a ResultSet means that you can use this StoredProcedure not only by a CALL statement
but also like any Sql Table applying WHERE, GROUP, ORDER, JOIN, .. whatever is available to a SELECT
This fact is not new but never mentioned in any documentation I know of.
Examples:
SELECT TOP 5 * FROM SYS.Process_JOBEXAM() ORDER BY Commands DESC
SELECT Name,Directory,MountRequired FROM Config.Databases_List() order by Name
My suggestion:
Understand a Stored Procedure returning a ResultSet as kind of a Sub-Select (with additional parameters).