· Nov 28, 2019

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. 

Discussion (11)1
Log in or sign up to continue

Perhaps you could expand on what you are trying to achieve. Where are you making the call from, odbc, objectscript, terminal? Which components are you working with?

One option may be to embed the call to the stored procedure in objectscript code,

   SET a=7,b="A",c=99
   &sql(CALL MyProc(:a,:b,:c))

Does that give you what you are looking for?

Hi Rupert, 

I hope you are well and enjoying robotics. 

Thanks for coming back to me.

Your recommended approach, though hugely appreciated, won't work for me I'm afraid. 

In answer to your questions:

  • I am making my call from object script, as part of an already built component within a financial cache application that explicitly uses a SELECT statement
  • Changing this component to use CALL instead of a SELECT is not possible due to dependencies and legacy code.

Essentially question I am asking is why some stored procedures can only be called via CALL and why can some be called by both CALL and SELECT (such as in the case of class queries)?

I get a feeling this has something to do with SQL-Invokable-Routine (SIR) - see @Dan Pasco comment here but there is almost nothing in the documentation or any code for me to go further on.

I believe this page explains why,  and I will also try my best to condense information:

  • Stored Procedures that return a single value are classified as Stored Functions. These can be SELECTed but not Stored Procedures.
  • Class Queries are SQL-Invokable-Routines (SIRs), but they have additional ClassMethods defined that allow it to be SELECTed. This is namely the <<name>>EXECUTE() and <<name>>FETCH() functions. This is reflected in the CREATE QUERY documentation that warns if EXECUTE() and FETCH() are not defined, SQLCODE -46 is thrown. (


Yes, SIR :) SQL Invokable routine. A procedure is invoked by CALL and a function is invoked using the function syntax. The primary difference between the two is in which statements you use to invoke the SIR. CALL invokes procedures, other DML statements can be used to invoke functions. The standard states that for every SQL Function there is an implicit procedure. That means that every SQL Function can also be invoked by CALL. The reverse is not necessarily true. IRIS SQL (and Caché SQL) does not enforce that restriction as even a procedure can be invoked using function syntax, as long as it returns a value. The issue with us doing that is that we do not restrict output-directed arguments and we do not restrict a Result Set Sequence (RSS) - strict SQL does not allow either in an SQL Function.  Just be careful - invoking such a procedure (with output directed parameters and/or returnresultsets) as a function will ignore all but the return value.

That said, where the SQL Function is located in a DML statement matters. In order for an SQL Function to be referenced in the FROM clause of a SELECT statement it must implement the Table-Valued Function (TVF) interface. Queries do that (I don't recall in which version we added that capability) and %SQL.CustomQuery does. Refer to the class documentation for %SQL.CustomQuery for more information. If you still have questions then let me know!

Defining a class method as a stored procedure will not work as it does not implement the TVF interface.


Perhaps this link will help -

Look specifically for the paragraph on table-valued functions.

What isn't especially clear in this document is how to implement a custom query. The simplest and most portable way to implement a custom query is to extend %SQL.CustomQuery. Another way is to define a class query whose type is %Query, define a ROWSPEC, and implement the various functions. It is much simpler to extend %SQL.CustomQuery. There should be good documentation in that class.

There was a bug in 2017.1 that caused some problems with the seamless projection of a %SQL.CustomQuery subclass as an SQL function. That bug has since been fixed but I do not know if it is fixed in 2017.2.2.

If you need more information, please let me know.