Use the CALL statement to call stored procedures that were created by using the CREATE PROCEDURE statement.
Parameters, clauses, keywords, and variables
▪ 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 one time.
– expression: Argument of the CALL statement. The expression must be a literal or a parameter marker (?), see expression.
The nth unnamed argument of the CALL statement corresponds to the nth parameter that is 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 the following 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.
However, if you specify a parameter‑name for one argument, you must use parameter names for all arguments.
▪ Positional parameters: Use the parameter position only, with no parameter‑name, and specify each expression in the same order as the corresponding parameter in the procedure definition.
You can omit arguments, that is, you can call a procedure with fewer arguments than the procedure definition specifies. 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 generates an error.
▪ Positional parameters can be omitted only in the order that is specified in the procedure definition, starting from the last parameter.
▪ ATnode‑ref: (supported only in Advanced Replication configurations that use remote stored procedures)
– On a master database: Calls the stored procedure that is to be executed on a replica database
– On a replica database: Calls the stored procedure that is to be executed on the master database.
▪ DEFAULT: Uses the current replica context. The current replica context is defined only when the procedure call is started in the background by 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 that is started by using the START AFTER COMMIT statement.
▪ 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, execute 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 call only 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 must either use the START AFTER COMMIT FOR EACH REPLICA statement or execute multiple CALL statements.
Transactions in remote procedure calls
A remote procedure call (whether or not it was started by a START AFTER COMMIT statement) 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 immediately after issuing the remote procedure call, the call is lost. It is not re-executed in the 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 (by using a START AFTER COMMIT statement), 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 by using a START AFTER COMMIT statement, 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.
Examples
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:
CALL participants(adults = 5, children = 2, pets = 3)
CALL participants(5, 2, 3)
However, 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 other parameters must also have a name.
CALL participants(adults = 5, 2, 3)
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 the value 2 for the adults parameter and default values for the children and pets parameters: