In Advanced Replication configurations, stored procedures can be called locally or remotely. Remote procedures are stored procedures that one database server calls from another database server. Remote stored procedure calls use the following syntax:

where server indicates the server where the remote stored procedure is located.

For the full syntax of the CALL statement, see CALL.

Remote stored procedures calls can be made only between two solidDB servers that have a master and replica relationship. The calls can be made in either direction; the master database can call a stored procedure on the replica database, or the replica database can call a stored procedure on the master database.

A remote stored procedure can be called from any context that allows a local procedure call. Thus, for example, you can call a remote stored procedure directly by using a CALL statement, or you can call the remote procedure from within a trigger, another stored procedure, or a START AFTER COMMIT statement, see START AFTER COMMIT.

A remote stored procedure can contain any command that any other stored procedure can contain. All stored procedures are created by using the same syntax rules. A single stored procedure might be called both locally and remotely at different times.

When called remotely, the stored procedure accepts parameters from the caller as if the call was local. However, a remote stored procedure cannot return a result set; it can return only an error code.

Both local and remote stored procedure calls are synchronous. Regardless of whether the procedure is called locally or remotely, the caller waits until the value is returned; the caller does not continue while the stored procedure executes in the background. If the stored procedure is called from inside a START AFTER COMMIT statement, the stored procedure call itself is synchronous, but the START AFTER COMMIT statement is not synchronous, so the stored procedure will execute as an asynchronous background process.

Transaction handling for remote stored procedures is different from transaction handling for local stored procedures. When a stored procedure is called remotely, the execution of the stored procedure is not a part of the transaction that contained the call. Therefore, you cannot roll back a stored procedure call by rolling back the transaction that called it.