Difference between %ResultSet.SQL and %SQL.Statement

From %ResultSet.SQL Class Reference:

This class has been superseded. It will remain for compatibility with existing code and samples. We recommend using %SQL.Statement.

In addition to the above: Dynamic SQL Using Older Result Set Classes

%SQL.Statement is part of the IRIS implementation of Dynamic SQL and it is based on the SQL Standard's Call Level Interface (CLI). It allows the user to prepare and execute any SQL statement, including DDL. The result of executing a dynamic statement is an instance of %SQL.StatementResult. That result contains at least %SQLCODE and %Message, indicating success/failure along with some minimal information about the failure.

Part of Dynamic SQL is also a result set interface - %SQL.IResultSet. There is at least one extention of %SQL.IResultSet, %SQL.ISelectResult. When a statement result is a result set or includes one or more result sets then those result sets are likely instances of %SQL.IResultSet.

The other result set classes (some deprecated) are not necessarily instances of %SQL.IResultSet but do implement the most common members of that interface.

There is another class, %SQL.CustomQuery, that can be used to implement custom queries that are more intuitive than class queries. The documentation for %SQL.CustomQuery contains information on how to implement your own custom query along with an example.