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.
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