Caché SQL Reference
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   

Invokes a stored procedure.
CALL procname(arg1,arg2,...) [USING contextvar]

retval=CALL procname(arg1,arg2,...) [USING contextvar]
procname The name of a stored procedure. May be a fully qualified name (including schema), or an unqualified name for which Caché supplies the schema name, as described below.
arg1 Optional — The actual argument list. One, or more values to pass to a stored procedure, specified in order as a comma-separated list. The parentheses are required, even when no arguments are specified.
USING contextvar Optionalcontextvar specifies a descriptor area variable that receives the procedure context object generated by the procedure call. If omitted, the default is %sqlcontext.
retval Optional — A variable specified to receive the procedure return value. Can contain a single value, not a result set. Can be specified as a local variable, a host variable, or a question mark (?) argument.
A CALL statement invokes a query exposed as an SQL stored procedure. The procname must be an existing stored procedure in the current namespace. If Caché cannot locate procname, it generates an SQLCODE -428 error. The procname must be a Stored Procedure with SqlProc=True. Refer to SqlProc in Caché Class Definition Reference.
The procname is not case-sensitive. You must append the argument parentheses to the procname, even if you are not specifying any arguments. Failing to do so results in an SQLCODE -1 error.
The CALL arg arguments are optional; the parentheses are required. This comma-separated list is known as the actual argument list, which must match in number and in sequence the formal argument list in the procedure definition. You may specify fewer actual argument values than the formal arguments defined in the stored procedure. If you specify more actual argument values than the formal arguments defined in the stored procedure, the system generates an SQLCODE -370 error. This error message specifies the name of the stored procedure, the number of arguments specified, and the number of arguments defined in the stored procedure.
You can omit trailing arguments; any missing trailing arguments are undefined and take default values. You can specify an undefined argument within the argument list by specifying a placeholder comma. For example, (arg1,,arg3) passes three arguments, the second of which is undefined. Commonly, undefined arguments take a default value that was specified when defining the stored procedure. If no default is defined, an undefined argument takes NULL. For further details refer to NULL and the Empty String in Using Caché SQL.
If you specify an argument value that does not match the data type defined in the stored procedure that argument takes NULL, even if a default value is defined. For example, a stored procedure defines an argument as IN numarg INT DEFAULT 99. If CALL specifies a numeric argument, that arg value is used. If CALL omits the argument, the defined default is used. However, if CALL specifies a non-numeric argument, NULL is used, not the defined default.
For further details on stored procedures, refer to the CREATE PROCEDURE command.
From Embedded SQL
ObjectScript embedded SQL can either issue a CALL statement, or use the DO command to invoke the underlying routine or method.
Using Embedded SQL, you can supply argument values to CALL as literals or by using any combination of :name host variables or question mark (?) input parameters, as follows:
   SET a=7,b="A",c=99
   &sql(CALL MyProc(:a,:b,:c))
   &sql(CALL MyProc(?,:b,?))
The initial invocation of a CALL statement in Embedded SQL creates an %sqlcontext variable, by default. Subsequent iterations use this existing %sqlcontext variable. This means that multiple iterations accumulate results in %sqlcontext that could potentially result in a <STORE> error. If a CALL statement is to be iterated repeatedly, you can explicitly specify the %sqlcontext variable in the USING clause. When a procedure context is specified in the USING clause Caché issues a NEW on that procedure context each time it is invoked.
A host variable used for an output arg can be a single value, an array reference, an reference, or a multidimensional reference.
You can return a value from a CALL statement by using either a host variable or a question mark (?):
   &sql(:rtnval=CALL MyProc())
   &sql(?=CALL MyProc())
The CALL return value must be a single value. You cannot return a result set from a CALL statement in Embedded SQL. Attempting to use retval=CALL syntax for a procedure that does not return a value generates an SQLCODE -371 error.
You can use the #SQLCompile Path macro directive to provide a schema search path that CALL can use to resolve an unqualified procedure name. If no #SQLCompile Path directive is defined, Caché uses the schema that maps to the package of the current class. If Caché cannot locate the specified procedure using either the schema search path or the schema default, it generates an SQLCODE -428 error.
For further details, refer to the Embedded SQL chapter of Using Caché SQL.
From Dynamic SQL
The following Dynamic SQL example calls the Stored Procedure Sample.PersonSets, which performs two queries on the Sample.Person table. The Stored Procedure arguments specify the WHERE clause values for these two queries. The first argument specifies to return all records in the first query where Name starts with arg1 (in this case, the letter “M”). The second argument specifies to return all records in the second query where Home_State = arg2 (in this case, “MA”):
  ZNSPACE "Samples"
  SET mycall = "CALL Sample.PersonSets(?,'MA')"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(mycall)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute("M")
  IF rset.%SQLCODE '= 0 {WRITE "SQL error=",rset.%SQLCODE  QUIT}
  DO rset.%Display()
The following Dynamic SQL example also calls the Stored Procedure Sample.PersonSets, returning the result sets for each query separately. The %Next() method iterates through the first query result set. The %MoreResults() method accesses the result set for the second query. If there were more than two queries, %MoreResults() would access each result set in turn.
  ZNSPACE "Samples"
  SET mycall = "CALL Sample.PersonSets(?,'MA')"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(mycall)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute("M")
  IF rset.%SQLCODE '= 0 {WRITE "SQL error=",rset.%SQLCODE  QUIT}
  WHILE rset.%Next() {
     WRITE "Name: ",rset.%Get("Name")
     IF rset.%Get("Spouse") {
     WRITE " Spouse: ",rset.%Get("Spouse"),!}
     ELSE {WRITE " unmarried",! }
  WRITE !,"1st row count=",rset.%ROWCOUNT,!!
   WHILE rset.%MoreResults() {
   DO rset.%CurrentResult.%Display()
Note that it is important to check the %SQLCODE value set by the CALL execution before invoking %Next(). Invoking the %Next() method sets %SQLCODE, overwriting the prior CALL %SQLCODE value. If %Next() receives no result set data, it sets %SQLCODE=100. It does not distinguish between an empty result set (no rows selected) and a nonexistent result set due to an error in CALL processing.
For further details on %SQL.Statement and on how to display a list of formal parameters and other metadata for a stored procedure, refer to the Using Dynamic SQL chapter of Using Caché SQL. The Returning the Full Result Set section of the “Using Dynamic SQL” chapter provides further information and examples of the %Display() method. The Returning Specific Values from the Result Set section of the “Using Dynamic SQL” chapter provides further information and examples of the %Next() and %Get() methods.
From ObjectScript
Rather than calling stored procedures directly from embedded SQL, you can invoke stored procedures through ObjectScript calls to the class methods that contain them. In this case, you have to manage the parameters, and with query-based stored procedures, the separate methods have to be called and the fetch loop managed.
For example, to call a method exposed as a stored procedure called UpdateAllAvgScores that has no arguments, the code is:
   NEW phnd
   SET phnd=##class(%SQLProcContext).%New()
   DO ##class(students).UpdateAllAvgScores(phnd)
   IF phnd.%SQLCODE {QUIT phnd.%SQLCODE}
   USE 0
   WRITE !,phnd.%ROWCOUNT," Rows Affected"
When specifying a procedure’s arguments in the call statement, you must not specify the %Library.SQLProcContext parameter if the procedure has an explicitly defined %Library.SQLProcContext parameter. The handling of this parameter is done automatically.
In the following example, the stored procedure takes two arguments. It has an explicitly defined procedure context.
   ZNSPACE "Samples"
   NEW phnd
   SET phnd=##class(%SQLProcContext).%New()
   SET rtn=##class(Sample.ResultSets).PersonSets("D","NY")
   IF phnd.%SQLCODE {QUIT phnd.%SQLCODE}
   DO %sqlcontext.%Display()
   WRITE !,"All Done"
To call a stored procedure that has been implemented as a query, you must call all three methods:
   NEW qhnd
   DO ##class(students).GetAvgScoreExecute(.qhnd,x1)
   NEW avgrow,AtEnd
   SET avgrow=$lb("") 
   SET AtEnd=0
   DO ##class(students).GetAvgScoreFetch(.qhnd,.avgrow,.AtEnd)
   SET x5=$lg(avgrow,1)
   DO ##class(students).GetAvgScoreClose(qhnd)
If a query-based stored procedure is to be nested within a number of other stored procedures, it is useful to write a wrapper method to hide all of this.
Caché fully supports CALL syntax as defined by the ODBC 2.x and JDBC 1.0 standards. In JDBC, you can invoked CALL through the methods of the CallableStatement class. In ODBC, there are APIs. The CALL syntax and semantics are exactly the same for JDBC and ODBC. Further, they are processed in the same way: both drivers parse the statement text and, if the statement is CALL, they directly invoke the special methods on the server side, bypassing the SQL engine.
If class PERSON has a stored procedure called SP1, then you can call this from an ODBC or JDBC client (such as Microsoft Query) as follows:
retcode = SQLExecDirect(hstmt, "{?=call PERSON_SP1(?,?)}", SQL_NTS);
Caché conforms to the ODBC standard in its structure for calling stored procedures. See the relevant documentation for more information on that standard.
With ODBC only, Caché allows relaxed syntax for calls, so there do not need to be curly braces around CALL or parentheses around parameters. (Since this is good programming form, the above example uses them.)
Again, with ODBC only, Caché allows modified syntax for using default parameters, so that CALL SP is different from CALL SP(). The second form implies passing of a default parameter — as does CALL SP (,,) or SP(,?,) or other such syntax. In that sense, the parenthesized form of CALL is different from non-parenthesized.
See Also