Replication of stored procedures

A stored procedure that is executed on the primary that updates replicated data is a replicated stored procedure.

Under the default replication mode, the stored procedure CALL statement is replayed on the replica by using by-SQL replication; the execution of the stored procedure is not replicated. In some cases, however, by-value replication is needed and supported.

Replication software supports any combination of calling a stored procedure that is defined in a connected replicated or non-replicated database. If all objects (tables and stored procedures) that are referenced in a stored procedure call hierarchy are replication set members, the top-level call is a candidate to execute by-SQL replication on the replica. Otherwise, the entire transaction that contains the top-level stored procedure call is a candidate to execute by-value replication on the replica. That is, the entire transaction uses by-value replication if anything is not suitable for by-SQL replication, such as references to unreplicated databases, references to temporary tables, and non-deterministic functions.

Candidate stored procedure calls are replicated only if they update replicated data (that is, perform UPDATE SQL on a non-temporary table in a replicated database). Stored procedure calls that do not update replicated data are not captured in the replication log. Dynamic SQL (that is, (execute immediate '...';) and DDL are both supported for replication in stored procedure calls in replicated databases.

Stored procedures fetch data and execute control statements that are based on the result set; they then might form fresh UPDATE queries. Because the SELECT queries that are used might reference other non-deterministic results, the procedures are handled by using by-value replication. That is, if a stored procedure selects non-deterministic or non-replicated data, the transaction replicates by value, even though it is not a write statement (INSERT, UPDATE, or DELETE).