Call Stored Procedure via a SELECT instead of a CALL
I have the following class:
Class temp.test7 [ Owner = {PHOENIXDBUSER} ] { ClassMethod GetTenAssets() As %Status [ ReturnResultsets, SqlProc ] { s sc=$$$OK s sql="SELECT TOP 10 * FROM Data_Assets.Asset" s sqlStatement=##class(%SQL.Statement).%New() s sc=sqlStatement.%Prepare(sql) if $$$ISOK(sc) { s result=sqlStatement.%Execute() d %sqlcontext.AddResultSet(result) s %sqlcontext.%SQLCODE=result.%SQLCODE s %sqlcontext.%ROWCOUNT=result.%ROWCOUNT s %sqlcontext.%Message=result.%Message } else { s %sqlcontext.%SQLCODE=-460,%sqlcontext.%Message=##class(%SYSTEM.Status).GetErrorText(sc) } q sc } }
I am able to successfully call this stored procedure using:
CALL temp.test7_GetTenAssets()
however I would ideally want to be able to call this stored procedure using a SELECT statement:
SELECT * FROM temp.test7_GetTenAssets()
- I am aware that documentation explicitly mentions using 'CALL' to execute a stored procedure. I, however, need to use a SELECT statement to align with how some of the existing components of the Cache application I am working with, are built.
- If 'CALL' is the only way to call this stored procedure then why is it possible to call a class query i.e. Query Get(x As %String) As %Query
(ROWSPEC = "FundCode:%String ...") [ SqlProc ] using a SELECT? I wonder which super class do class queries extend that allows them to be Stored Procedures and still be executed via a SELECT. Does any one know?
Any help will be greatly appreciated.