question

Rob Allen avatar image
Rob Allen asked

What is the best way to refactor a series of complicated If statements into a table-based rules engine

I have a taken over a handful of scalar UDfs in SQL Server 2005 that each return the current status of a particular business object. For the sake of argument, let's use the concept of a product order (purchase).

The UDFs all follow a similar sequence of events:

  • declare a bunch of variables
  • select data into those variables
  • run If statements until you hit a return statement
  • if no other return is found, return EverythingIsFine

These UDFs not only slow my reports to a crawl but they are cumbersome to maintain. I have heard that there is a way to build out a table-based rules engine, but my searches for good information have missed the mark.

What suggestions do you have for refactoring this type of nonsense into a cache-able query?

sql-server-2005user-defined-function
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered

Dunno, but I've got some of that "nonsense" :) too and would like to find a rules-based answer.

I do have one suggestion though:

We used to have "Exceptions Reports". They would do a bunch of CASE / IF statement tests and build errors messages. For example, for a product, they would alert if the image was missing / had no Width/Height, was bigger than set-limits, Product had no assigned category ...

This was OK, but you had to run the Exception Report to see the messages for a given product.

We changed this to have a (single) Errors Table. It had columns for [Table], [PK], [ErrorNo], [ErrorMsg]

We created a query that would make the tests, as-per the exception report, but store them in the Errors Table.

We added a step to the INSERT/UPDATE trigger which ran the ErrorsCheck on the modified records and cleared / recreated the ErrorsTable entry for that record's PK.

We were then able to display the Errors on the Product Maintenance Page - instead of people having to remember to run the Exception Report.

So ... could you "cache" the results of the "business rules test" such that they were always available?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered

Hi Rob,

Could you post a few rows of data and some of the rules that would act on this data. I have some ideas but wanted to have a concrete solution. Thx

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

graz avatar image
graz answered

I'm hoping these are table-valued functions and not scalar functions called in the column list of a SELECT statement.

In either case, you may be able to put the 80/20 rule to work for you. You might be able to satisfy 80% of your results with just a few rows. Consider building something like this:

Cond1   Cond2   Cond3   Result            
  A       1       X       Ok            
  A       2       X       Ok            
  A       1       Z      Fail            
  B      NULL     X       Ok            

You can join to this table and get the result. You'll need to have some way to ignore join columns that have NULLs. You might also need to prioritize results in case more than one result matches.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.