The CALL statement is used for calling stored procedures that have been created with the CREATE PROCEDURE statement. To call a stored procedure, the caller must have EXECUTE privilege on the procedure.
argument
parameter-name
Name of the parameter to which the argument is assigned.
When an argument is assigned to a parameter by name, all the arguments that follow it must also be assigned by name. A named argument can be specified only once.
expression
Each specification of expression is an argument of the CALL. The expression must be a literal or a parameter marker (?)
The nth unnamed argument of the CALL statement corresponds to the nth parameter defined in the CREATE PROCEDURE statement for the procedure. If the procedure definition specified default values for the parameters, you do not have to give values for all the parameters.
You can enter arguments in either of two ways:
Named parameters
Use a parameter name (in the form parameter_name = expression), even if the arguments are not in the same order as the parameters.
Important: If you specify a parameter_name for one argument, you must use parameter names for all the arguments that follow it.
Positional parameters
Use the parameter position only, with no parameter_name, specifying each expression is in the same order as the parameter in the procedure definition to which the argument corresponds.
You can omit arguments, that is, you call a procedure with fewer arguments than the procedure definition specified. The solidDB® server initializes missing arguments to their corresponding default values.
The following rules apply to omitting parameters:
▪You can omit only parameters for which a default value is specified. If the missing arguments have no default values, solidDB® issues an error.
▪Positional parameters can be omitted only in the order specified in the procedure definition, starting from the last parameter.
ATnode_ref
The AT node_ref clause is supported only in advanced replication configurations that use remote stored procedures; it can be executed from a master node to one of its replica nodes or vice-versa.
DEFAULT
DEFAULT means that the current replica context is used. The current replica context is only defined when the procedure call is started in the background using the START AFTER COMMIT statement with the FOR EACH REPLICA option. If the default is not set, the error Default node not defined is returned. DEFAULT can be used inside stored procedures and in a statement started with START AFTER COMMIT.
replica_name
Defines the name of the replica node where the remote procedure is executed.
For example, to call the procedure remote_proc on the replica node called replica2, issue the following statement
CALL remote_proc AT replica2;
master_name
Defines the name of the master node where the remote procedure is executed.
A remote stored procedure cannot return a result set; it can only return an error code.
A single call statement can only call a single procedure on a single node. If you want to call more than one procedure on a single node, you must execute multiple CALL statements. If you want to execute the same procedure (with the same procedure name) on more than one node, you have to either use the START AFTER COMMIT FOR EACH REPLICA statement or execute multiple calls.
Transactions in remote procedure calls
A remote procedure call (whether or not it was started by a START AFTER COMMIT) is executed in a separate transaction from the transaction that it was called from. The caller cannot roll back or commit the remote procedure call. The procedure that is executing in the called node is responsible for issuing its own commit or rollback statement.
Also, remote procedure calls are not durable. If the server goes down right after issuing the remote procedure call, the call is lost. It will not be executed in recovery phase.
Return values from the remote procedure
When you call a remote stored procedure, you cannot get a complete result set returned. Instead, the server returns the return value of the stored procedure (a single value) or an error code.
Note If the remote procedure is executed in the background (using START AFTER COMMIT), no return value is returned to the user. Even error codes are not returned.
A procedure call is executed synchronously; it returns after the call is executed. However, if the procedure call is executed using START AFTER COMMIT (for example, START AFTER COMMIT UNIQUE CALL FOO AT REPLICA1), the procedure call is executed asynchronously in the background. The asynchronous execution is a characteristic of the START AFTER COMMIT statement, not of the procedure call.
Example: Calling a stored procedure without default parameter values
The procedure participants has been created in the database as follows:
"CREATE PROCEDURE participants( adults integer , children integer , pets integer ) BEGIN END"
The following procedure calls return identical results:
Note The following syntax fails because the parameter name has been defined for only one argument (adults). If a name is given to a parameter, all parameters following it must also have a name.
CALL participants(adults = 5, 2, 3)
The following syntax succeeds because the positional parameters
Example: Calling stored procedures with default parameter values
The procedure paying_guests has been created in the database as follows:
"CREATE PROCEDURE paying_guests( adults integer = 1, children integer = '0', pets integer = '0') BEGIN END"
The following procedure calls use value 2 for parameter adults and default values for parameters children and pets: