You are creating a payroll processing system. At the end of each pay cycle, the system must compute the payroll for that pay cycle based upon the records of the employees' hours and various configuration tables such as taxes, deductions, etc. The data in the configuration tables will be administered by an application front-end, but the accounting department has a tendency to add business rules that need to be evaluated by your payroll system. For instance, HR may implement a new employee rewards program that automatically gives all technical support employees a small bonus if they handle a high volume of support calls. Ideally, you could add this functionality without writing, testing, and deploying a completely new version of the payroll application.

This situation is ideally suited to using interface-based programming techniques. You write your application to process some variable list of business rules in a specified order. You then write the business rules that you need to satisfy today's requirements, and, as new requirements arise, you write new rules to handle new requirements and simply add them to the list of rules your application processes and you've satisfied the new requirements.

In theory, this design is great, but how do you implement such a thing? If you were writing your payroll processing application in some COM-enabled language, you could simply define an IBusinessRule interface and code your main processing application to call this interface in order to invoke a business rule. You could then create an implementation of this IBusinessRule interface for each rule that you need to process. Whenever you need a new business rule, you simply create a new implementation of the IBusinessRule interface, add the ProgID of the COM class implementing this interface to some configuration file, and then your application then supports this new rule.

For those of you not familiar with COM, the same concept holds in any number of object-oriented or semi object-oriented programming languages. C++ and Java use this concept with abstract base classes, .NET via various incarnations of implementation inheritance, Visual Basic 5 and 6 via the Implements keyword... the list goes on. In general object-oriented programming, the concept of interfaced-based programming is known as polymorphism.

SQL is by no means an object-oriented language; rather, it is a declarative language that deals in terms of sets of data and works best when you use it this way. However, simply because SQL is not an object-oriented language does not mean that you cannot apply some of the key concepts of interface-based programming. At its roots, interface-based programming is simply a matter of defining how one type of thing can pass data to another type of thing so that both things can understand it. At one level or another, quite a few concepts in computer programming have the concept of interface. For example, the Windows API is a well-defined set of functions that Windows programs use to communicate with the OS. Certainly with this loose definition of an interface, we can implement such a thing in SQL.

In most all relational database management systems, the SQL language provides an interface to the underlying data structures used by the RDBMS to store the data. An application does not have to understand B-trees in order read data from SQL Server. The application simply submits an SQL query defining the needed data and SQL Server returns the requested data. Thus, SQL is an interface defined by SQL Server.

SQL certainly is a useful interface to SQL Server, but it doesn't help us very much with our payroll problem. It would be quite difficult to write an application that was capable of generating the appropriate SQL statement for whatever business rules the accounting or HR departments could dream up and still have any sort of reasonable configuration user interface. There must be something that we are missing about interface-based programming that will help us along. After all, we have an interface into SQL Server, but it seems quite difficult to apply it to our payroll problem.

When looking at interfaces, there is a great amount of variance in the complexity. A simple C++ abstract base class that defines three member functions certainly has less configuration options than an entire programming language like SQL. However, having fewer configuration options is not to be confused with having less power. Consider a normal, everyday Windows EXE. In essence, the OS has a single interface to the EXE; it can execute it. However, once run, the EXE can receive input any number of ways: via a user interface, from files, or from external devices.

Along the same lines, a C++ class that implements an abstract base class with three member functions can receive input from places other than the interface its implementing. Consider the following class definition:

class IBusinessRule {


public:


    virtual void Initialize(LPCSTR lpszSQLServer,LPCSTR lpszSQLDatabase) = 0;


    virtual int ProcessRule() = 0;


    virtual void Terminate() = 0;


}





class CCalculateFICA : public IBusinessRule {


public:


    virtual void Initialize(LPCSTR lpszSQLServer,LPCSTR lpszSQLDatabase);


    virtual int ProcessRule();


    virtual void Terminate();


}


The Initialize member function is passed the server name and database and can then establish a connection to the database. This is an example of receiving input via the interface. However, the ProcessRule member function takes no arguments, yet, it performs a much more complicated function than does Initialize. The ProcessRule member function "knows" what it is supposed to do because the programmer who implemented it had the ability to encode the rule's functionality using the full power of C++.

However, we are not presently concerned with implementing our business rules in C++. Every time a business rule changes, we don't want to have to write a new bit of C++, compile it into binary format, and distribute it to all the places our application is installed. We want to define some useful interface to SQL that is simple, self-contained, and relatively easy to replace. Ideally, it would be much like the C++ abstract base class in having only a few configuration options, but being able to do a broad array of things based upon those configuration options. It turns out that SQL Server has the ideal candidate: stored procedures.

A SQL Server stored procedure is basically a bit of stored SQL code that takes some defined list parameters. When writing applications that call stored procedures, the stored procedure name is passed as a string to SQL Server. Depending of the data access API used to interface with SQL Server, parameters are either passed as text or in some binary format representative of their data type. Thus, in writing data access code, it is relatively easy to switch out stored procedure names as long as they take the same set of parameters. In the case of our payroll application, all we have to do is define some standard set of parameters to be passed to a "rule" stored procedure. For instance, if we had five different rules to be implemented, we simply create five different stored procedures, all taking the same parameter list:

CREATE PROCEDURE pr_PayrollRule_CalculateGrossPay


        @PayrollPeriod int,


        @PayrollGroupID int





CREATE PROCEDURE pr_PayrollRule_CalculateFICA


        @PayrollPeriod int,


        @PayrollGroupID int





CREATE PROCEDURE pr_PayrollRule_CalculateSocSecurity


        @PayrollPeriod int,


        @PayrollGroupID int





CREATE PROCEDURE pr_PayrollRule_CalculateStateTax


        @PayrollPeriod int,


        @PayrollGroupID int





CREATE PROCEDURE pr_PayrollRule_Calculate401k


        @PayrollPeriod int,


        @PayrollGroupID int





The main application that calls each business rule could then be configured to call each rule in the proper order. This can easily be accomplished by creating a configuration table listing the rules along with the associated stored procedure and execution order.

CREATE TABLE tb_PayrollRule(


        PayrollRuleID             int                 NOT NULL IDENTITY(1, 1),


        RuleName                  varchar(50)         NOT NULL,


        RuleProcedure             varchar(128)        NOT NULL,


        ExecutionOrder            int                 NOT NULL


)





INSERT INTO tb_PayrollRule(RuleName, RuleProcedure, ExecutionOrder)


        VALUES('Calculate Gross Pay', 'pr_PayrollRule_CalculateGrossPay', 1)





INSERT INTO tb_PayrollRule(RuleName, RuleProcedure, ExecutionOrder)


        VALUES('Calculate FICA Withholding', 'pr_PayrollRule_CalculateFICA', 2)





INSERT INTO tb_PayrollRule(RuleName, RuleProcedure, ExecutionOrder)


        VALUES('Calculate Social Security Withholding', 'pr_PayrollRule_CalculateSocSecurity', 3)





INSERT INTO tb_PayrollRule(RuleName, RuleProcedure, ExecutionOrder)


        VALUES('Calculate State Tax Withholding', 'pr_PayrollRule_CalculateStateTax', 4)





INSERT INTO tb_PayrollRule(RuleName, RuleProcedure, ExecutionOrder)


        VALUES('Calculate 401(k) Withholding', 'pr_PayrollRule_Calculate401k', 5)





Then, in order to retrieve the correct procedures to execute, simply issue the following query:

SELECT


        RuleProcedure


FROM


        tb_PayrollRule


ORDER BY


        ExecutionOrder


The main processing application can then call each procedure in the order specified. This application could be written in your programming language of choice. Also, because you are calling stored procedures rather than generating SQL code on the fly, you gain the full benefits of having compiled, reusable execution plans in SQL Server. If a rule changes, it is quite easy to write and deploy a new version of the rule by issuing an ALTER PROCEDURE statement and making the appropriate changes to the stored procedure.

Oftentimes, business rules need to be processed in the context of a transaction. This is easily accomplished by having the main processing application issue a BEGIN TRANSACTION statement before executing the first rule and a COMMIT TRANSACTION or ROLLBACK TRANSACTION upon completion of processing.

By determining the names of the stored procedures being called at runtime rather than hardcoding them into your application, you also gain flexibility in choosing the procedures to be called. You could implement a lookup table and call different stored procedures based parameters passed into your application. You could also specify effective date ranges for particular business rules. You can use fully-qualified stored procedure names to call procedures in different databases or even different servers. The possibilities are endless.

About the Author

Chris Cubley is an MCSD with over four years of experience designing and implementing SQL Server-based solutions in the education, healthcare, and telecommunications industries. He can be reached at ccubley@queryplan.com.