Skip to main content

CALL (SQL)

Invokes a stored procedure.

Synopsis

CALL procname(arg_list) [USING contextvar]

retval=CALL procname(arg_list) [USING contextvar]

Description

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 InterSystems IRIS cannot locate procname, it generates an SQLCODE -428 error. The procname must be a Stored Procedure with SqlProc=True. Refer to SqlProc.

For further details on stored procedures, refer to the CREATE PROCEDURE command.

Arguments

procname

The name of an existing stored procedure. The procname must be followed by parentheses, even if no arguments are specified. A procedure name can take any of of the following forms:

  • Unqualified: Takes the default schema name. For example, MedianAgeProc().

  • Qualified: Supplies a schema name. For example, Patient.MedianAgeProc().

  • Multilevel: Qualified with one or more schema levels to paralell corresponding class package members. In this case, the procname may contain only one period character; the other periods in the corresponding class method name are replaced with underline characters. The period is specified before the lowest level class package member. For example, %SYSTEM.SQL_GetROWID(), or %SYS_PTools.StatsSQL_Export().

InterSystems IRIS locates the match for an unqualified procname in a schema, using either the default schema name, or (if provided) a schema name from the schema search path. If InterSystems IRIS cannot locate the specified procedure using either the schema search path or the system-wide schema default, it generates an SQLCODE -428 error. You can use the $SYSTEM.SQL.Schema.Default()Opens in a new tab method to determine the current system-wide default schema name. The initial system-wide default schema name is SQLUser, which corresponds to the class package name User.

To determine if a procname exists in the current namespace, use the $SYSTEM.SQL.Schema.ProcedureExists()Opens in a new tab method. 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.

arg_list

A list of arguments used to pass values to the stored procedure. The arg_list is enclosed in parentheses and arguments in the list are separated by commas. The parentheses are mandatory, even if you specify no arguments.

The arg_list arguments are optional. 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.

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.

An arg_list argument can be a user-defined function (a method stored procedure that returns a value).

USING contextvar

An optional argument. contextvar specifies a descriptor area variable that receives the procedure context object generated by the procedure call. If omitted, the default is %sqlcontext.

retval

An optional 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.

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, meaning 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 InterSystems IRIS 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 oref.property reference, or a multidimensional oref.property 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.

For further details, refer to Embedded 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”):

  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()Opens in a new tab method iterates through the first query result set. The %MoreResults()Opens in a new tab method accesses the result set for the second query. If there were more than two queries, %MoreResults() would access each result set in turn.

  #include %occStatus
  set mycall = "CALL Sample.PersonSets(?,'MA')"
  set tStatement = ##class(%SQL.Statement).%New()
  set qStatus = tStatement.%Prepare(mycall)
  if $$$ISERR(qStatus) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(qStatus) quit}

  set rset = tStatement.%Execute("M")
  if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

FirstResultSet
  while rset.%Next()
  {
    write "Name: ",rset.%Get("Name")
    if rset.%Get("Spouse") {write " Spouse: ",rset.%Get("Spouse"),!}
    else {write " unmarried",!}
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  write !,"1st row count=",rset.%ROWCOUNT,!!

SecondResultSet
   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.StatementOpens in a new tab and on how to display a list of formal parameters and other metadata for a stored procedure, refer to Using Dynamic SQL. Also, Returning the Full Result Set provides further information and examples of the %Display() method. Returning Specific Values from the Result Set 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.

   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.

From ODBC or JDBC

InterSystems IRIS fully supports CALL syntax as defined by the ODBC 2.x and JDBC 1.0 standards. In JDBC, you can invoke 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, you can call this from an ODBC or JDBC client (such as Microsoft Query) as follows:

retcode = SQLExecDirect(hstmt, "{?=call PERSON_SP1(?,?)}", SQL_NTS);

InterSystems IRIS 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, InterSystems IRIS allows relaxed syntax for calls, so there does 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, InterSystems IRIS 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

FeedbackOpens in a new tab