Implementing an Applied Stored Procedure

Learn the steps to implement an applied stored procedure.

Prerequisites

Verify that you have completed the asynchronous stored procedure prerequisites.

Task

See Replication Server Reference Manual > RSSD Stored Procedures for information about stored procedures used to query the RSSD for system information.

Set up replicate databases that contain replicate tables. These tables may or may not match the replication definition for the primary table.
As necessary, set up routes from the primary Replication Server to the replicate Replication Servers that have subscriptions to replication definitions for the primary table.

See Replication Server Administration Guide Volume 1 > Manage Routes.
Locate or create a replication definition on the primary Replication Server that identifies the table to be modified.

See Replication Server Administration Guide Volume 1 > Manage Replicated Tables.
In the primary database, mark the table for replication using the sp_setreptable system procedure.
For example, for a table named employee, enter:
```
sp_setreptable employee, 'true'
```
Follow the guidelines when specifying stored procedures and tables and for replication. For sp_setreptable, the single quotes are optional. See Replication Server Administration Guide Volume 1 > Manage Replicated Tables > Mark Tables for Replication > Use the sp_setreptable System Procedure.
Create the stored procedure on the primary database.
The first statement in the stored procedure must contain an update command for the first row of the primary table. For example:
```
create proc upd_emp
 @emp_id int, @salary float
 as
 update employee
 set salary = salary * @salary
 where emp_id = @emp_id
```
Warning! If the first statement in the stored procedure contains an operation other than update, Replication Server cannot distribute the stored procedure to replicate databases. Check the warning conditions. Never include dump transaction or dump database commands in the stored procedure. If the stored procedure contains commands with statement level errors, the error may occur at the replicate DSI. Depending on the error actions, the DSI may shut down.
In the primary database, mark the stored procedure for replication using sp_setrepproc.
For example, enter:
```
sp_setrepproc upd_emp, 'table'
```
Follow the guidelines when specifying stored procedures and tables and for replication. See Replication Server Administration Guide Volume 1 > Manage Replicated Functions > Mark Stored Procedures for Replication.
At the replicate Replication Servers, create subscriptions to a replication definition for the table that the stored procedure at the primary database updates.

See Replication Server Administration Guide Volume 1 > Manage Subscriptions.

Warning! Be sure the replicate database subscribes to both the before image and after image of the updated row. If it does not, Replication Server cannot distribute the stored procedure to the replicate database.
Create a stored procedure on the replicate database with the same name and parameters as the stored procedure on the primary database, but do not mark the procedure as replicated.
For example:
```
create proc upd_emp
 @emp_id int, @salary float
 as
 update employee
 set salary = salary * @salary
 where emp_id = @emp_id
```
Grant execute permission on the stored procedure to the maintenance user.
For example:
```
grant execute on upd_emp to maint_user
```
Create a user-defined function on the primary Replication Server that associates the stored procedure to the name of a replication definition for the table it updates.
For example:
```
create function employee_rep.upd_emp
 (@emp_id int, @salary float)
```
Only one user-defined function is shared by all replication definitions for the same table. You can specify the name of any of these replication definitions.
Verify that all Replication Server and database objects in all the steps exist at the appropriate locations.