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
- if no other
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?