User-Defined Functions and Static Filters

User-defined functions are subroutines composed of encapsulated sets of Transact-SQL logic. You can use them in row static or dynamic filters.

By accessing user-defined functions, you increase your filtering capability because you can create filters based on frequently performed logic, table-driven business rules, or any set of complex instructions that returns a value.

You can specify user-defined functions that return a scalar value (such as int, char, or decimal) when filtering horizontally (row filtering replicates a subset of the rows in a table) in snapshot replication, transactional replication, or merge replication.

To create a user-defined function for use as a publication filter, use the CREATE FUNCTION command on the database containing the data you want to publish, and build a function with Transact-SQL. You can then use the function in a filter when you create a new publication using the Create Publication Wizard or when configuring an existing publication using the publication properties dialog box. If the publication has subscribers, you must drop all subscriptions to the publication before you can create or modify row filters. You do not have to replicate the function to use it as part of a filter in a publication.

Example

This statement creates a function that returns the week number of any date that is indicated in the parameter:

CREATE FUNCTION fn_wknum(@Parm datetime)
RETURNS  int
AS
BEGIN
  DECLARE @ReturnVar int
  SELECT @ReturnVar = CAST((DATEPART(dy,@Parm) + DATEPART(dw,@Parm-DATEPART(dy, @Parm)-1))/7+1 AS int)
  RETURN @ReturnVar
END

To implement the fn_wknum example in a publication based on the Northwind database, create the function on that database. Start the Create Publication Wizard, select Define Data Filters, and then in the Filter Table Rows dialog box, click the properties button (...) for the Orders article.

In the Specify Filter dialog box, you can complete the WHERE clause to filter for the first 12 weeks of any year based on the orderdate column:

SELECT * FROM [dbo].[Orders] WHERE dbo.fn_wknum(orderdate) between 1 and 12

To filter with a user-defined function using the Create Publication Wizard

Enterprise Manager

To filter with a user-defined function using the publication properties dialog box

Enterprise Manager

To drop all subscriptions to a publication

Enterprise Manager

User-Defined Functions and Static Filters

See Also