CALL (SQL)

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() 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() 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.

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).

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.

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.