CALL
Synopsis
CALL procname(arg_list) [USING contextvar] retval=CALL procname(arg_list) [USING contextvar]
Arguments
Argument | Description |
---|---|
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. |
arg_list | 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 | Optional — contextvar 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. |
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 Caché cannot locate procname, it generates an SQLCODE -428 error. The procname must be a Stored Procedure with SqlProc=True. Refer to SqlProc in Class Definition Reference.
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().
Caché 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 Caché 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.DefaultSchema()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.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 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.
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 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.
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()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.
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}
FirstResultSet
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,!!
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 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.
From ODBC or JDBC
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
-
SQL statements: CREATE PROCEDURE, CREATE QUERY, CREATE METHOD
-
ObjectScript: DO command
-
“Defining and Using Stored Procedures” chapter in Using Caché SQL.
-
SQLCODE error messages listed in the Caché Error Reference