The CALL command runs a stored procedure.
Syntax
A) As function: SET VAR vVariable TYPE = (CALL procname(arglist))
B) As command: CALL procname(arglist)
About the CALL Command
The call command is used to invoke a stored procedure that was created by using the SET PROCEDURE command. It can be referenced by either a function notation or as a stand-alone command. In either case the argument list must be included. If you wish to include a blank argument list then use an empty pair of parenthesis.
Both methods of using CALL have their advantages. For example, using the function notation allows you to use a stored procedure in a computed view or to invoke a stored procedure via an SQL statement. On the other hand, used as a command, you will be able to reference the stored procedure by itself.
When using the Function notation the return value of the stored procedure is stored in the variable itself or displayed in the column (in the case of a computed column in a table or view). When using the Command notation the return value of the stored procedure will be placed into the system variable STP_RETURN. STP being an abbreviation of stored procedure.
The CALL command is supported in EEPs.
Examples
In the example below, a view is using a stored procedure to calculate values from another table.
CREATE VIEW MonthSum (CustomerID,CustomerSummary) +
AS SELECT T1.CustomerID,(CALL SumUpCust(T1.CustomerID)) FROM Customers T1
In the following example, an SQL select statement is used to invoke a maintenance routine from another application, written in Visual Basic, Java, or some other development environment. The use of WHERE LIMIT=1 causes the procedure to run once and only once. Without this clause the stored procedure would execute once for every matching row in the table. The AnyTable can be any table in the database in this case. The only requirement is that we must use a table in order to have a "healthy" SELECT clause.
SELECT (CALL DBCheck()) FROM AnyTable WHERE LIMIT=1
