Hi Grant,
As you've seen, the NS framework is geared to toward cases where developers
define the match rules and users just customize them through parameters.
Cases where you want users to define the rules (custom queries) are a bit
more difficult to handle, but can be done.

The first recommendataion I give to people facing this situation is to
question whether they really need this flexibility. In most cases, the
developer can come up with a set of fixed subscription classes that cover
most, if not all, likely customer queries.

If ultimately you really do need end-users to design rules within a single
subscription class, here are two strategies you can use (the examples below
are from an application that sends notifications to insurance agents about
claims that have been filed).

You can build a subscription class that encompasses all possible conditions
(or at least the most common conditions) that users want to use in their
rules. Then, you make some of the fields optional, so that if the user doesn
't specify a value, that condition is ignored. I'll try to illustrate this
with an example. Let's say your event class for a claim has fields,
LastName, ClaimAmount, and Date. You could then have a subscription class
with fields: LastName, AmountGreaterThan, AmountLessThan, DateAfter, and
DateBefore. Then you create a match rule that looks like:

SELECT ClaimNotify(..)
FROM ClaimEvents e, ClaimSubscriptions s
WHERE e.LastName = s.LastName
AND ((s.AmountGreaterThan IS NULL) OR (e.Amount > s.AmountGreaterThan))
AND ((s.AmountLessThan IS NULL) OR (e.Amount < s.AmountLessThan))
AND ((s.DateAfter IS NULL) OR (e.Date > s.DateAfter))
AND ((s.DateBefore IS NULL) OR (e.Date < s.DateBefore))
Because each of the WHERE clauses first checks if the subscription column is
null, users can pick and choose from the conditions they want in their
rules. They still can only pick from the ones you provide, but presumably
you could provide a comprehensive list. Note that the performance of this
query may be quite bad if you have a lot of conditions. The exact
performance you get out of it will depend on the exact schema, the indexes
you have on the tables etc. I don't know what kind of volume you're
expecting in your application, so I don't know whether or not the
performance will be sufficient.

There is one other possibility, but this one is comes with even greater
performance concerns. When a user creates a subscription, you can create a
custom query for that subscription. This query acts like a mini match rule
for that subscription only. You can actually store these queries in a table
and then run them from the real match rule. For example, let's say you have
one user who creates a subscription that says:

Notify me of any claims where LastName = Smith and Amount > 500 and Date >
6/1/2004



Imagine that the subscription ID for this subscription is 7. You can turn
this into the following query:


SELECT ClaimNotify(s.SubscriberId, .)

FROM ClaimEvents e, ClaimSubscriptions s

WHERE e.LastName = 'Smith' AND e.Amount > 500 AND e.Date > 6/1/2004

AND s.SubscriptionId = 7

Another user might create this subscription:


Notify me of any claims where LastName = Jones and Date < 1/1/2000



If this is subscription ID 8, you can turn it into the following query:

SELECT ClaimNotify(s.SubscriberId, .)

FROM ClaimEvents e, ClaimSubscriptions s

WHERE e.LastName = 'Jones' AND e.Date < 1/1/2000

AND s.SubscriptionId = 8

You then store these in a table. The table might have the following schema:


CREATE TABLE SubscriptionQueries

(

SubscriptionId bigint,

QueryText nvarchar(4000)

)

In the actual match rule for the subscription class, you open a cursor over
this table and iteratively execute each of the queries with sp_executesql.

This approach works, but because you are executing one query per
subscription, the scale is limited. With a lot of subscriptions, evaluating
all these queries can be costly. You can mitigate this to some extent by sha
ring queries among several subscriptions that have conditions in common, but
this makes your app significantly more complex.

Hope this helps.
-shyam

--
---------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
---------------------------------------------
[quoted text, click to view]