Stored Procedure Filters

You can create your own stored procedure to filter data and implement partitioning. The filter stored procedure must follow this format:

CREATE PROCEDURE replica_filter FOR REPLICATION
AS


IF sql_statement

RETURN 1

ELSE

RETURN 0

For example, to create a stored procedure used to publish the authors_publication article with only the authors who live in California, add the following steps to your replication setup script.

USE pubs

GO

CREATE PROCEDURE ca_authors_filter FOR REPLICATION

AS

IF EXISTS (SELECT state FROM authors (NOLOCK) WHERE state = 'CA')

RETURN 1

ELSE

RETURN 0

GO

Note Filter stored procedures have the following limitations:

• Filter stored procedures may contain only SELECT, DECLARE, GOTO, and conditional statements.
• Filter stored procedures cannot reference text, image, or ntext fields in the parent table.