Choosing a Rules Engine Design

For those that don’t know. A rules engine is a way to harness powerful decision-making based on source data. These decisions are defined in rules. Rules will generally consist of a query that outputs a list of successes or failures, depending on what needs to be reported on. In my experience, I have only found two major methods for building a rules-based engine in SQL Server. They both have their pros and cons. The trick is finding the middle ground which is defined as a third method between the two different extremes that will fit your requirement. With that said we will discuss the two extremes — and the middle ground. The Three Kinds of SQL Rules Engines Extreme 1 – The Dynamic SQL Rules Engine The first method involves dynamic SQL. Using dynamic SQL, the sky is the limit in regards to possibilities. The downfall however is maintainability. Dynamic SQL is very hard to maintain and even harder to troubleshoot and debug. My experience dealing with dynamic SQL is that it often starts out small, but once you’ve gone the dynamic route, it gets bigger and bigger and harder to maintain. Therefore I try to avoid this method altogether. If you’re into it, you’ll definitely find more job security, I’m not into that game personally. Extreme 2 – The Hard-coded Rules Engine The next method is to write each rule as a hardcoded procedure. When the rule executes, it returns back the list of results that fail (or pass) […]