ADS 10 Tip #12 – Results of a Stored Procedure in a SELECT

Advantage now allows you to use the results of a stored procedure or system procedure as a table in a SELECT clause. This allows you to select specific columns, filter the results and even join the results to other tables or the results of other procedures.

// Using stored procedure with select clause
SELECT Name, Description, CodePage FROM (execute procedure sp_getcollations(NULL)) gc
WHERE CodePage = 1202

In an earlier tip I discussed the express queue and how it is used to prioritize worker threads. You can get information about the express queue using system procedures. In the example below I am using the results from two of these system procedures to get a list of which applications are currently running below the express queue threshold.

// Get a list of connections below the EQThreshold
SELECT UserName, ApplicationID, AverageCost FROM 
  (EXECUTE PROCEDURE sp_mgGetConnectedUsers()) u
WHERE AverageCost < 
  ( SELECT EQThreshold FROM (EXECUTE PROCEDURE sp_mgGetActivityInfo()) a )
ORDER BY AverageCost DESC

Here is a snapshot of the query results

For a demonstration of the new SQL features in Advantage 10 you can watch this screencast on the DevZone.